create or replace PACKAGE PK_SOM_MST IS

  -- Author  : HOP.DANG
  -- Created : 2013-02-04 8:52:26 AM
  -- Purpose : For Sell-Out management
  -------------------------------------------------------------------------------------------
  const_inct_out_period CONSTANT      VARCHAR2(10) :='INC_OU_PR';
  const_inct_out_model  CONSTANT      VARCHAR2(10) :='INC_OU_MD1'; --'INC_OU_MD';
  const_inct_valid      CONSTANT      VARCHAR2(10) :='INC_IN_VL'; --'INC_IN_VL';
  const_wt_inct_valid   CONSTANT      VARCHAR2(10) :='INC_IN_VL1'; --'INC_WT_VL';
  const_inct_wr_channel CONSTANT      VARCHAR2(10) :='INC_O_CHN'; -- before 2014's Jan 28 'INC_BL_NVL';
  
  const_wt_24_vl        CONSTANT      VARCHAR2(10) :='WT_SN_VL24';
  const_wt_12_vl        CONSTANT      VARCHAR2(10) :='WT_SN_VL12';
   
  -------------------------------------------------------------------------------------------
  --                         INCENTIVE SCHEME & CLOSING
  -------------------------------------------------------------------------------------------
  FUNCTION FN_INCT_SCHEME_CRT(         
           p_region                   VARCHAR2
          ,p_scheme_name              VARCHAR2
          ,p_purpose                  VARCHAR2
          ,p_start_date               VARCHAR2
          ,p_end_date                 VARCHAR2
          ,p_create_user              VARCHAR2)
  RETURN                              VARCHAR2;
  -------------------------------------------------------------------------------------------
  PROCEDURE PR_INCT_SCHEME_INQ(
            out_cur            OUT    SYS_REFCURSOR
           ,p_scheme_id        IN     VARCHAR2      DEFAULT null
           ,p_scheme_name      IN     VARCHAR2      DEFAULT null
           ,p_create_mm        IN     VARCHAR2      DEFAULT null
           ,p_user_region      IN     VARCHAR2      DEFAULT '00'
           ,p_region_id        IN     VARCHAR2      DEFAULT '%');
  -------------------------------------------------------------------------------------------  
  FUNCTION FN_INCT_CLOSING_CRT(       
           p_yyyymm                   VARCHAR2
          ,p_s_date                   VARCHAR2
          ,p_e_date                   VARCHAR2
          ,p_scheme_id                VARCHAR2 
          ,p_user                     VARCHAR2
          ,p_purpose                  VARCHAR2)                                                                          
  RETURN                              VARCHAR2;
  -------------------------------------------------------------------------------------------  
  FUNCTION FN_INCT_CLOSING(
           p_period                   VARCHAR2
          ,p_scheme_id                VARCHAR2
          ,p_user                     VARCHAR2)
  RETURN                              VARCHAR2;
  -------------------------------------------------------------------------------------------  
  PROCEDURE PR_INCT_CLOSED_INQ(
            out_cur             OUT   SYS_REFCURSOR
           ,p_closed_period     IN    VARCHAR2
           ,p_user_region       IN    VARCHAR2      DEFAULT '00'
           ,p_region_id         IN    VARCHAR2      DEFAULT '%'
           ,p_option            IN    VARCHAR2      DEFAULT 'SUMMARY'
           ,p_shop_code         IN    VARCHAR2      DEFAULT null
           ,p_shop_name         IN    VARCHAR2      DEFAULT null);
  -------------------------------------------------------------------------------------------
  --                         VALIDATE & SAVE INCENTIVE BY MODEL
  -------------------------------------------------------------------------------------------
  FUNCTION FN_INCT_MD_UPL_VAL        
          (p_scheme_id                VARCHAR2
          ,p_model                    VARCHAR2
          ,p_point                    VARCHAR2
          ,p_amt                      VARCHAR2)
  RETURN                              VARCHAR2;                                        
  -------------------------------------------------------------------------------------------
  FUNCTION FN_INCT_MD_UPL_SV(          
           p_scheme_id                VARCHAR2
          ,p_model                    VARCHAR2
          ,p_point                    VARCHAR2
          ,p_amt                      VARCHAR2
          ,p_upload_user              VARCHAR2)
  RETURN                              VARCHAR2;
  -------------------------------------------------------------------------------------------
  PROCEDURE PR_INCT_MD_INQ(
            out_cur            OUT    SYS_REFCURSOR
           ,p_scheme_id        IN     VARCHAR2
           ,p_model            IN     VARCHAR2      DEFAULT null);
  -------------------------------------------------------------------------------------------
  --                         VALIDATE SELL-OUT INCENTIVE 
  -------------------------------------------------------------------------------------------
  FUNCTION FN_SO_INCT_VAL(            
           p_shop_code                VARCHAR2
          ,p_model                    VARCHAR2
          ,p_serial_no                VARCHAR2
          ,p_sellout_date             VARCHAR2)
  RETURN                              INCT_STATITICS;    
  -------------------------------------------------------------------------------------------                                          
  --                         CONFIRM SELL-OUT INCENTIVE
  -------------------------------------------------------------------------------------------
  PROCEDURE PR_SO_INCT_SET_CFM(       
            p_shop_code               VARCHAR2
           ,p_shop_cell               VARCHAR2
           ,p_serial_no               VARCHAR2
           ,p_model                   VARCHAR2
           ,p_suffix                  VARCHAR2
           ,p_sellout_date            VARCHAR2
           ,p_cfm_user                VARCHAR2
           ,p_sms_yn                  VARCHAR2);
  -------------------------------------------------------------------------------------------
  FUNCTION FN_SO_INCT_DEALER_SET_MNL_CFM(
            p_shop_code               VARCHAR2
           ,p_shop_cell               VARCHAR2 
           ,p_edi_no                  VARCHAR2
           ,p_serial_no               VARCHAR2
           ,p_model                   VARCHAR2
           ,p_suffix                  VARCHAR2
           ,p_sellout_date            VARCHAR2
           ,p_sellin_date             VARCHAR2
           ,p_user_cell               VARCHAR2
           ,p_cfm_comment             VARCHAR2
           ,p_cfm_user                VARCHAR2)
  RETURN                              VARCHAR2;         
  -------------------------------------------------------------------------------------------
  PROCEDURE PR_SO_INCT_AUTO_CFM;
  -------------------------------------------------------------------------------------------
  PROCEDURE PR_SO_INCT_BAL_SV(
            p_region                  VARCHAR2
           ,p_scheme                  VARCHAR2
           ,p_shop_code               VARCHAR2
           ,p_model                   VARCHAR2
           ,p_serial_no               VARCHAR2
           ,p_point                   NUMBER
           ,p_amt                     NUMBER
           ,p_yyyymm                  VARCHAR2);
  -------------------------------------------------------------------------------------------
  PROCEDURE PR_SO_INCT_BAL_SHOP_INQ(
            out_cur             OUT   SYS_REFCURSOR
           ,p_ms_yyyymm         IN    VARCHAR2      DEFAULT TO_CHAR(sysdate,'YYYYMM')
           ,p_me_yyyymm         IN    VARCHAR2      DEFAULT TO_CHAR(sysdate,'YYYYMM')
           ,p_shop_name         IN    VARCHAR2      DEFAULT null
           ,p_user_region       IN    VARCHAR2      DEFAULT '00'
           ,p_region_id         IN    VARCHAR2      DEFAULT '%');
  -------------------------------------------------------------------------------------------
  PROCEDURE PR_SO_INCT_BAL_BILL_INQ(
            out_cur             OUT   SYS_REFCURSOR
           ,p_ms_yyyymm         IN    VARCHAR2      DEFAULT TO_CHAR(sysdate,'YYYYMM')
           ,p_me_yyyymm         IN    VARCHAR2      DEFAULT TO_CHAR(sysdate,'YYYYMM')
           ,p_billto_name       IN    VARCHAR2      DEFAULT null
           ,p_user_region       IN    VARCHAR2      DEFAULT '00'
           ,p_region_id         IN    VARCHAR2      DEFAULT '%');         
  -------------------------------------------------------------------------------------------
  --                         PROCEDURES SELL-OUT SERIAL UPLOAD VALIDATION & SAVE
  -------------------------------------------------------------------------------------------
  FUNCTION FN_SO_UPL_VAL(             
           p_shop_code                VARCHAR2
          ,p_model                    VARCHAR2      DEFAULT null
          ,p_serial_no                VARCHAR2
          ,p_end_user_cell            VARCHAR2)
  RETURN                              VARCHAR2;
  -------------------------------------------------------------------------------------------
  FUNCTION FN_SO_UPL_SV(             
           p_vrf_flag                 VARCHAR2
          ,p_shop_code                VARCHAR2
          ,p_model                    VARCHAR2      DEFAULT null
          ,p_serial_no                VARCHAR2
          ,p_end_user_cell            VARCHAR2      DEFAULT null)   
  RETURN                              VARCHAR2;
  -------------------------------------------------------------------------------------------                                     
  --                         PROCEDURES SHOP SELL-OUT INQUIRYING
  -------------------------------------------------------------------------------------------
  PROCEDURE PR_SO_MTH_INQ(            
            out_cur           OUT     SYS_REFCURSOR
           ,p_yyyy            IN      VARCHAR2      DEFAULT TO_CHAR(sysdate,'YYYY')
           ,p_shop_code       IN      VARCHAR2      DEFAULT '%'
           ,p_product         IN      VARCHAR2      DEFAULT '%'
           ,p_model           IN      VARCHAR2      DEFAULT '%'
           ,p_user_region     IN      VARCHAR2      DEFAULT '00'
           ,p_region_id       IN      VARCHAR2      DEFAULT '%');
  -------------------------------------------------------------------------------------------
  PROCEDURE PR_SO_WKL_QTY_INQ(
            out_cur           OUT     SYS_REFCURSOR
           ,p_ws_date         IN      VARCHAR2      DEFAULT TO_CHAR(TRUNC(sysdate,'Day')+1 ,'YYYYMMDD')
           ,p_we_date         IN      VARCHAR2      DEFAULT TO_CHAR(TRUNC(sysdate,'Day')+7 ,'YYYYMMDD')
           ,p_shop_name       IN      VARCHAR2      DEFAULT null
           ,p_product         IN      VARCHAR2      DEFAULT '%'
           ,p_model           IN      VARCHAR2      DEFAULT null
           ,p_user_region     IN      VARCHAR2      DEFAULT '00'
           ,p_region          IN      VARCHAR2      DEFAULT '%');         
  -------------------------------------------------------------------------------------------
  PROCEDURE PR_SO_WKL_INQ(           
            out_cur           OUT     SYS_REFCURSOR
           ,p_yyyymm          IN      VARCHAR2      DEFAULT TO_CHAR(sysdate, 'YYYYMM')
           ,p_shop_code       IN      VARCHAR2      DEFAULT '%'
           ,p_product         IN      VARCHAR2      DEFAULT '%'
           ,p_model           IN      VARCHAR2      DEFAULT '%'
           ,p_user_region     IN      VARCHAR2      DEFAULT '00'
           ,p_region_id       IN      VARCHAR2      DEFAULT '%');
  -------------------------------------------------------------------------------------------
  PROCEDURE PR_SO_DLY_INQ(         
            out_cur           OUT     SYS_REFCURSOR
           ,p_ws_date         IN      VARCHAR2      DEFAULT TO_CHAR(TRUNC(sysdate,'Day') +1,'YYYYMMDD')
           ,p_we_date         IN      VARCHAR2      DEFAULT TO_CHAR(TRUNC(sysdate,'Day') +7,'YYYYMMDD')
           ,p_shop_code       IN      VARCHAR2      DEFAULT '%'
           ,p_product         IN      VARCHAR2      DEFAULT '%'
           ,p_model           IN      VARCHAR2      DEFAULT '%'
           ,p_user_region     IN      VARCHAR2      DEFAULT '00'
           ,p_region_id       IN      VARCHAR2      DEFAULT '%');
  -------------------------------------------------------------------------------------------
  PROCEDURE PR_SO_DLY_REG_INQ(       
            out_cur           OUT     SYS_REFCURSOR
           ,p_ds_date         IN      VARCHAR2      DEFAULT TO_CHAR(sysdate, 'YYYYMMDD')
           ,p_de_date         IN      VARCHAR2      DEFAULT TO_CHAR(sysdate, 'YYYYMMDD')
           ,p_shop_code       IN      VARCHAR2      DEFAULT null
           ,p_shop_name       IN      VARCHAR2      DEFAULT null
           ,p_prod_l1         IN      VARCHAR2      DEFAULT null
           ,p_model           IN      VARCHAR2      DEFAULT null
           ,p_incentive_flag  IN      VARCHAR2      DEFAULT '%'
           ,p_sms_yn          IN      VARCHAR2      DEFAULT '%'
           ,p_user_region     IN      VARCHAR2      DEFAULT '00'
           ,p_region_id       IN      VARCHAR2      DEFAULT '%'
           ,p_serial_no       IN      VARCHAR2      DEFAULT null);
   -------------------------------------------------------------------------------------------
   PROCEDURE PR_SO_DLY_HIS_INQ(
            out_cur           OUT     SYS_REFCURSOR      
           ,p_df_date         IN      VARCHAR2      DEFAULT TO_CHAR(sysdate, 'YYYYMMDD')
           ,p_dt_date         IN      VARCHAR2      DEFAULT TO_CHAR(sysdate, 'YYYYMMDD')
           ,p_hist_type       IN      VARCHAR2      DEFAULT pk_sms_mst.const_cmdcode_so
           ,p_user_region     IN      VARCHAR2      DEFAULT '00'
           ,p_region_id       IN      VARCHAR2      DEFAULT '%'
           ,p_resp_type       IN      VARCHAR2      DEFAULT '%'
           ,p_shop_cell       IN      VARCHAR2      DEFAULT null
           ,p_mo_msg          IN      VARCHAR2      DEFAULT null);         
  -------------------------------------------------------------------------------------------
  PROCEDURE PR_SO_NON_INCT_INQ(
            out_cur           OUT     SYS_REFCURSOR
           ,p_f_date          IN      VARCHAR2      DEFAULT TO_CHAR(TRUNC(sysdate,'Day') +1, 'YYYYMMDD')
           ,p_t_date          IN      VARCHAR2      DEFAULT TO_CHAR(TRUNC(sysdate,'Day') +7, 'YYYYMMDD')
           ,p_shop_name       IN      VARCHAR2      DEFAULT null
           ,p_product         IN      VARCHAR2      DEFAULT '%'
           ,p_model           IN      VARCHAR2      DEFAULT '%'
           ,p_serial_no       IN      VARCHAR2      DEFAULT '%'
           ,p_user_region     IN      VARCHAR2      DEFAULT '00'
           ,p_region_id       IN      VARCHAR2      DEFAULT '%') ;          
  -------------------------------------------------------------------------------------------
  PROCEDURE PR_SO_DEALER_INQ(
            out_cur           OUT     SYS_REFCURSOR
           ,p_shop_code       IN      VARCHAR2     DEFAULT null
           ,p_shop_name       IN      VARCHAR2     DEFAULT null
           ,p_f_dt            IN      VARCHAR2     DEFAULT TO_CHAR(sysdate,'YYYYMMDD')
           ,p_t_dt            IN      VARCHAR2     DEFAULT TO_CHAR(sysdate +1,'YYYYMMDD')
           ,p_vrf_flag        IN      VARCHAR2     DEFAULT null
           ,p_cfm_flag        IN      VARCHAR2     DEFAULT 'N'
           ,p_user_region     IN      VARCHAR2     DEFAULT '00'
           ,p_region_id       IN      VARCHAR2     DEFAULT '%');
 PROCEDURE  PR_MAKE_DEALER_SO;          
  -------------------------------------------------------------------------------------------
  --                         PROCERDURES BILL_TO INVENTORY INQUIRYING
  -------------------------------------------------------------------------------------------
  PROCEDURE PR_INV_AG_MTH_INQ(       
            out_cur           OUT     SYS_REFCURSOR
           ,p_yyyy            IN      VARCHAR2      DEFAULT TO_CHAR(sysdate, 'YYYY')
           ,p_billto_code     IN      VARCHAR2      DEFAULT '%'
           ,p_product         IN      VARCHAR2      DEFAULT '%'
           ,p_model           IN      VARCHAR2      DEFAULT '%'
           ,p_user_region     IN      VARCHAR2      DEFAULT '00'
           ,p_region_id       IN      VARCHAR2      DEFAULT '%');
  -------------------------------------------------------------------------------------------
  PROCEDURE PR_INV_AG_WKL_INQ(        
            out_cur           OUT     SYS_REFCURSOR 
           ,p_yyyymm          IN      VARCHAR2      DEFAULT TO_CHAR(sysdate,'YYYYMM')
           ,p_billto_code     IN      VARCHAR2      DEFAULT '%'
           ,p_product         IN      VARCHAR2      DEFAULT '%'
           ,p_model           IN      VARCHAR2      DEFAULT '%' 
           ,p_user_region     IN      VARCHAR2      DEFAULT '00'
           ,p_region_id       IN      VARCHAR2      DEFAULT '%');                                   
  -------------------------------------------------------------------------------------------                             
  PROCEDURE PR_INV_AG_DLY_INQ(   
            out_cur           OUT     SYS_REFCURSOR
           ,p_ws_date         IN      VARCHAR2      DEFAULT TO_CHAR(TRUNC(sysdate, 'Day') +1,'YYYYMMDD')    
           ,p_we_date         IN      VARCHAR2      DEFAULT TO_CHAR(TRUNC(sysdate, 'Day') +7,'YYYYMMDD')
           ,p_billto_code     IN      VARCHAR2      DEFAULT '%'
           ,p_product         IN      VARCHAR2      DEFAULT '%'
           ,p_model           IN      VARCHAR2      DEFAULT '%'
           ,p_user_region     IN      VARCHAR2      DEFAULT '00'
           ,p_region_id       IN      VARCHAR2      DEFAULT '%');  
  -------------------------------------------------------------------------------------------
  PROCEDURE PR_INV_SUM_INQ(          
            out_cur           OUT     SYS_REFCURSOR
           ,p_ds_date         IN      VARCHAR2      DEFAULT TO_CHAR(TRUNC(sysdate, 'Day') +1,'YYYYMMDD') 
           ,p_de_date         IN      VARCHAR2      DEFAULT TO_CHAR(TRUNC(sysdate, 'Day') +7,'YYYYMMDD')
           ,p_billto_code     IN      VARCHAR2      DEFAULT '%'
           ,p_product         IN      VARCHAR2      DEFAULT '%'
           ,p_model           IN      VARCHAR2      DEFAULT '%'
           ,p_user_region     IN      VARCHAR2      DEFAULT '00'
           ,p_region_id       IN      VARCHAR2      DEFAULT '%');
  -------------------------------------------------------------------------------------------
  PROCEDURE PR_PROD_REG_PND_INQ(     
            out_cur           OUT     SYS_REFCURSOR
           ,p_ds_date         IN      VARCHAR2      DEFAULT TO_CHAR(TRUNC(sysdate,'Day') +1,'YYYYMMDD')
           ,p_de_date         IN      VARCHAR2      DEFAULT TO_CHAR(TRUNC(sysdate,'Day') +7,'YYYYMMDD')
           ,p_product         IN      VARCHAR2      DEFAULT null
           ,p_model           IN      VARCHAR2      DEFAULT null
           ,p_serial_no       IN      VARCHAR2      DEFAULT null
           ,p_sms_yn          IN      VARCHAR2      DEFAULT null
           ,p_pnd_type        IN      VARCHAR2      DEFAULT pk_sms_mst.const_sn_pnd_vl
           ,p_shop_name       IN      VARCHAR2      DEFAULT null
           ,p_user_region     IN      VARCHAR2      DEFAULT '00'
           ,p_region_id       IN      VARCHAR2      DEFAULT '%');
  -------------------------------------------------------------------------------------------
  PROCEDURE PR_PROD_WT_PND_INQ(     
            out_cur           OUT     SYS_REFCURSOR
           ,p_ds_date         IN      VARCHAR2 DEFAULT TO_CHAR(sysdate,'YYYYMMDD')
           ,p_de_date         IN      VARCHAR2 DEFAULT TO_CHAR(sysdate,'YYYYMMDD') 
           ,p_product         IN      VARCHAR2 DEFAULT null
           ,p_model           IN      VARCHAR2 DEFAULT null
           ,p_serial_no       IN      VARCHAR2 DEFAULT null
           ,p_pnd_type        IN      VARCHAR2 DEFAULT pk_sms_mst.const_wt_sn_nvl);
  -------------------------------------------------------------------------------------------
  PROCEDURE PR_PROD_WT_PER_INQ(
            out_cur           OUT     SYS_REFCURSOR
           ,p_serial_no       IN      VARCHAR2
           ,p_model           IN      VARCHAR2      DEFAULT '%');
  -------------------------------------------------------------------------------------------
  FUNCTION FN_MNL_ACT_SO_SAV(
           p_pick_no                  VARCHAR2
          ,p_serial_no                VARCHAR2
          ,p_box_no                   VARCHAR2      DEFAULT null
          ,p_model                    VARCHAR2
          ,p_suffix                   VARCHAR2      DEFAULT null
          ,p_prod_date                VARCHAR2
          ,p_act_user                 VARCHAR2)
  RETURN                              VARCHAR2;
  -------------------------------------------------------------------------------------------
  FUNCTION FN_MNL_ACT_PND_REJ(
           p_serial_no                VARCHAR2
          ,p_model                    VARCHAR2 DEFAULT null
          ,p_user                     VARCHAR2)
  RETURN                              VARCHAR2;
  -------------------------------------------------------------------------------------------
  PROCEDURE PR_SHOP_INQ(     
            out_cur           OUT     SYS_REFCURSOR
           ,p_shop_code       IN      VARCHAR2      DEFAULT null
           ,p_shop_name       IN      VARCHAR2      DEFAULT null
           ,p_shop_address    IN      VARCHAR2      DEFAULT null
           ,p_shop_cell       IN      VARCHAR2      DEFAULT null
           ,p_use_flag        IN      VARCHAR2      DEFAULT null
           ,p_dealer_yn       IN      VARCHAR2      DEFAULT null
           ,p_user_region     IN      VARCHAR2      DEFAULT '00'
           ,p_region_id       IN      VARCHAR2      DEFAULT '%');      
  ------------------------------------------------------------------------------------------- 
  --                         PROCEDURES TRACING : ORDER/PICK/BILLTO/MODEL/SERIAL_NO
  -------------------------------------------------------------------------------------------
  PROCEDURE PR_ORDER_TRC(  
            out_cur           OUT     SYS_REFCURSOR
           ,p_order_no        IN      VARCHAR2 );
  -------------------------------------------------------------------------------------------                                     
  PROCEDURE PR_PICK_TRC(
            out_cur           OUT     SYS_REFCURSOR
           ,p_billto_code     IN      VARCHAR2
           ,p_model           IN      VARCHAR2
           ,p_suffix          IN      VARCHAR2      DEFAULT '%');
  -------------------------------------------------------------------------------------------          
  PROCEDURE PR_SN_TRC(     
            out_cur           OUT     SYS_REFCURSOR
           ,p_serial_no       IN      VARCHAR2
           ,p_model           IN      VARCHAR2      DEFAULT null);
  -------------------------------------------------------------------------------------------
  PROCEDURE PR_PROD_MODEL_TRC(
            out_cur           OUT     SYS_REFCURSOR
           ,p_model           IN      VARCHAR2);
  -------------------------------------------------------------------------------------------
  PROCEDURE PR_BILLTO_TRC(
            out_cur           OUT     SYS_REFCURSOR
           ,p_bill_shop_name  IN      VARCHAR2);                                                       
END PK_SOM_MST;

/

create or replace PACKAGE BODY PK_SOM_MST IS
  -----------------------------------------------------------------------------------------------------
  --                           FUNCTION CREATE INCENTIVE CLOSING INCENTIVE
  -----------------------------------------------------------------------------------------------------
  FUNCTION FN_INCT_CLOSING_CRT(       
           p_yyyymm             VARCHAR2
          ,p_s_date             VARCHAR2
          ,p_e_date             VARCHAR2
          ,p_scheme_id          VARCHAR2 
          ,p_user               VARCHAR2
          ,p_purpose            VARCHAR2)                                                                          
  RETURN                        VARCHAR2
  -----------------------------------------------------------------------------------------------------
  IS                            PRAGMA AUTONOMOUS_TRANSACTION;
   -- v_month                     DATE := null;
  -----------------------------------------------------------------------------------------------------
  BEGIN
    --v_month                  := TO_DATE(p_yyyymm, 'YYYYMM');
      
    INSERT INTO                 TB_CM_INCENTIVE_CLOSE_MST(
                                close_period
                               ,start_date
                               ,end_date
                               ,scheme_id
                               ,create_user
                               ,purpose)
    SELECT         
                                p_yyyymm
                               ,p_s_date
                               ,p_e_date
                               ,p_scheme_id
                               ,p_user
                               ,p_purpose
    FROM                        DUAL;
    
    COMMIT;
    RETURN                      'DONE';
  EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
      RETURN                    'DUPLICATED';
        
  END FN_INCT_CLOSING_CRT;
  -----------------------------------------------------------------------------------------------------
  --                           FUNCTION INCENTIVE CLOSING     
  -----------------------------------------------------------------------------------------------------
  FUNCTION FN_INCT_CLOSING(
           p_period             VARCHAR2
          ,p_scheme_id          VARCHAR2
          ,p_user               VARCHAR2)
  RETURN                        VARCHAR2
  -----------------------------------------------------------------------------------------------------
  IS                            PRAGMA AUTONOMOUS_TRANSACTION;
    v_period                    VARCHAR2(006) := null;
    v_start_date                VARCHAR2(008) := null;
    v_end_date                  VARCHAR2(008) := null;
    
    v_month_amt_msg             VARCHAR2(160) := null;
    v_msg_resp_type             VARCHAR2(010) := null;
    v_cmd_code                  VARCHAR2(004) := null;
    v_p1                        VARCHAR2(007) := null;
    v_cell                      VARCHAR2(020) := null;
    
 --   cur_inc                     cursor;
    
  -- v_pending_cfm               VARCHAR2(04) := null;  
  -- v_return                    VARCHAR2(01) := null;
  -----------------------------------------------------------------------------------------------------
  BEGIN
    SELECT                      close_period
                               ,start_date
                               ,end_date
    INTO                        v_period
                               ,v_start_date
                               ,v_end_date         
    FROM                        TB_CM_INCENTIVE_CLOSE_MST
    WHERE  close_period       = p_period
    AND    scheme_id          = p_scheme_id;
    
   /* SELECT count(*)
    FROM   TB_SN_PND_HIST
    WHERE   */
    UPDATE TB_SN_SO_WT_MST
    SET    close_flag         = 'Y'
          ,close_period       = v_period
          ,close_user         = p_user
    WHERE TO_CHAR(sellout_date, 'YYYYMMDD') BETWEEN v_start_date AND v_end_date
    AND   incentive_cfm_period IN (p_scheme_id, 'WTNA', 'NA') 
    AND   incentive_cfm_flag   = 'Y';
    
    UPDATE TB_CM_INCENTIVE_CLOSE_MST
    SET    close_flag          = 'Y'
          ,close_user          = p_user
          ,close_date          = sysdate
    WHERE  close_period        = v_period
    AND    scheme_id           = p_scheme_id;
    
    COMMIT;
    
    ---------------------------------------------------------------------------
    FOR cur_inc IN(
       SELECT s.shop_code
             ,SUM(im.amt)      sum_amt
             ,s.cell_no#1      shop_cell
       FROM   tb_sn_so_wt_mst  im
             ,tb_cm_shop_inf   s 
       WHERE  im.shop_code   = s.shop_code
       AND    TO_CHAR(sellout_date,'YYYYMMDD') BETWEEN v_start_date AND v_end_date
       AND    im.close_period= v_period
       AND    im.close_flag  = 'Y'  
     --  AND    amt            > 0
       GROUP BY s.shop_code, s.cell_no#1)
     LOOP
       IF TO_NUMBER(cur_inc.sum_amt) > 0 THEN
         v_cmd_code            := pk_sms_mst.const_cmdcode_so;
         v_msg_resp_type       := pk_sms_mst.const_inc_amt_m;
         v_p1                  := SUBSTR(v_period,5,2) ||'/'|| SUBSTR(v_period, 1,4);
         v_cell                := cur_inc.shop_cell;
         
         v_month_amt_msg       := pk_sms_mst.FN_GET_MT_RESPONSE(v_msg_resp_type,
                                                 '$' || 'thang '||v_p1 ||'$'|| TRIM(TO_CHAR(cur_inc.sum_amt,'999,999,999')) ||' VND');
                                                 
         
         INSERT INTO   TB_MT_HIST(moseq                         ,cell_no
                                 ,cmdcode                       ,resp_type
                                 ,msgbody                       ,mo_msgbody
                                 ,mo_receive_time               ,mt_seq)
         VALUES                  ('0'                           ,v_cell
                                 ,v_cmd_code                    ,v_msg_resp_type
                                 ,v_month_amt_msg               ,cur_inc.shop_code              
                                 ,sysdate                       ,mt_seq.nextval);
         COMMIT;
       END IF;   
     END LOOP;    
    ---------------------------------------------------------------------------   */            
    RETURN                      'DONE';
  EXCEPTION
    WHEN OTHERS THEN RETURN SQLERRM;  
  END FN_INCT_CLOSING;
  -----------------------------------------------------------------------------------------------------
  --                           PROCEDURE CLOSED INCENTIVE INQUIRY (TOTAL)
  -----------------------------------------------------------------------------------------------------
  PROCEDURE PR_INCT_CLOSED_INQ(
            out_cur             OUT SYS_REFCURSOR
           ,p_closed_period     IN  VARCHAR2
           ,p_user_region       IN  VARCHAR2      DEFAULT '00'
           ,p_region_id         IN  VARCHAR2      DEFAULT '%'
           ,p_option            IN  VARCHAR2      DEFAULT 'SUMMARY'
           ,p_shop_code         IN  VARCHAR2      DEFAULT null
           ,p_shop_name         IN  VARCHAR2      DEFAULT null)
  -----------------------------------------------------------------------------------------------------
  IS
    v_region_id                     VARCHAR2(02)  := null;
    v_s_date                        VARCHAR2(08)  := null;
    v_e_date                        VARCHAR2(08)  := null;
           
  BEGIN
    IF p_user_region                           = '00' THEN
      v_region_id                             := p_region_id;
    ELSE
      v_region_id                             := p_user_region;
    END IF;
    
    SELECT start_date
          ,end_date
    INTO   v_s_date
          ,v_e_date
    FROM   TB_CM_INCENTIVE_CLOSE_MST
    WHERE  close_period                        = p_closed_period;
    
    IF p_option                                = 'SUMMARY' THEN                 
      OPEN out_cur FOR
        SELECT CASE WHEN p_user_region ='00' THEN rg.name END
                                                REGION
              ,UPPER(p.name)                    PROVINCE
              ,sh.shop_code                     CUST_CODE
              ,UPPER(sh.shop_name)              CUST_NAME
              ,UPPER(sh.address)                ADDRESS
              ,b.billto_code                    BILL_CODE
              ,b.billto_name                    BILL_NAME
              ,COUNT(so.serial_no)              QTY
              ,TO_NUMBER(SUM(so.amt)+SUM(so.point),'999999999')           
                                                INCT_AMT
              ,sc.scheme_name                   SCHEME         
        FROM   TB_SN_SO_WT_MST                  SO
              ,TB_CM_SHOP_INF                   SH
              ,(SELECT distinct(billto_name)
                      ,billto_code 
                      ,serial_no
                      ,model
                FROM  VW_SN_BILLTO  )           B
              ,TB_CM_REGION                     RG
              ,TB_CM_INCENTIVE_MST              SC
              ,TB_CM_PROVINCE                   P
        WHERE  sh.reg_id                       = rg.id
        AND    sh.province_code                = p.id       
        AND    so.model                        = b.model(+)
        AND    so.serial_no                    = b.serial_no(+)
        AND    so.shop_code                    = sh.shop_code
        AND    so.incentive_cfm_period         = sc.scheme_id(+)
        AND    TO_CHAR(so.sellout_date,'YYYYMMDD')     
                                         BETWEEN v_s_date AND v_e_date
        AND    rg.id                        LIKE v_region_id                                   
        AND    so.close_flag                   = 'Y'
        AND    sh.shop_code                 LIKE DECODE(p_shop_code, null, '%', '%'||UPPER(p_shop_code)||'%')
        AND    UPPER(sh.shop_name)          LIKE DECODE(p_shop_name, null, '%', '%'||UPPER(p_shop_name)||'%')   
        GROUP BY 
               rg.name
              ,sh.shop_code                     
              ,sh.shop_name
              ,sh.address 
              ,p.name                   
              ,b.billto_code                    
              ,b.billto_name
              ,sc.scheme_name                    
        ORDER BY
               rg.name
              ,p.name 
              ,sh.shop_code                     
              ,sh.shop_name;
    ELSIF p_option                                = 'DETAIL' THEN
      OPEN out_cur FOR
        SELECT r.name                           REGION
              ,UPPER(p.name)                    PROVINCE
              ,s.shop_code                      SHOP_CODE 
              ,UPPER(s.shop_name)               SHOP_NAME
              ,UPPER(s.address)                 ADDRESS
              ,b.billto_code                    BILL_CODE
              ,b.billto_name                    BILL_NAME
              ,m.prod_l1                        PRODUCT
              ,so.model                         MODEL
              ,COUNT(so.serial_no)              QTY
              ,TO_NUMBER(SUM(so.point),'999999999') 
                                                WT_AMT
              ,TO_NUMBER(SUM(so.amt),'999999999')
                                                INCT_AMT
              ,t.scheme_name                    SCHEME
        FROM   TB_SN_SO_WT_MST                  SO         
              ,TB_CM_REGION                     R
              ,TB_CM_PROVINCE                   P
              ,TB_CM_SHOP_INF                   S
              ,(SELECT distinct(billto_name)
                      ,billto_code 
                      ,serial_no
                      ,model
                FROM  VW_SN_BILLTO  )           B
              ,TB_CM_MODEL_CAT                  M
              ,TB_CM_INCENTIVE_MST              T
        WHERE  s.reg_id                       = r.id
        AND    s.province_code                = p.id
        AND    s.shop_code                    = so.shop_code
       -- AND    s.billto_code                  = b.billto_code(+)
        AND    so.model                       = m.model(+)
        AND    so.serial_no                   = b.serial_no(+)
        AND    so.model                       = b.model(+)
        AND    so.incentive_cfm_period        = t.scheme_id(+)
        AND    TO_CHAR(so.sellout_date,'YYYYMMDD')                
                                       BETWEEN  v_s_date AND v_e_date
        AND    r.id                        LIKE v_region_id
        AND    so.close_flag                  = 'Y'
        AND    s.shop_code                 LIKE DECODE(p_shop_code, null, '%', '%'||UPPER(p_shop_code)||'%')
        AND    UPPER(s.shop_name)          LIKE DECODE(p_shop_name, null, '%', '%'||UPPER(p_shop_name)||'%')
        GROUP BY
               r.name
              ,s.shop_code
              ,s.shop_name
              ,p.name
              ,s.address
              ,b.billto_code
              ,b.billto_name
              ,m.prod_l1
              ,so.model
              ,t.scheme_name 
        ORDER BY
               r.name
              ,p.name 
              ,s.shop_code 
              ,s.shop_name
              ,m.prod_l1
              ,so.model;
    ELSE
      OPEN out_cur FOR
        SELECT r.name                           REGION
              ,UPPER(p.name)                    PROVINCE
              ,s.shop_code                      SHOP_CODE 
              ,UPPER(s.shop_name)               SHOP_NAME
             -- ,UPPER(s.address)                 ADDRESS
              ,bt.billto_code                   BILLTO_CODE
              ,bt.billto_name                   BILLTO_NAME
              ,m.prod_l1                        PRODUCT
              ,so.model                         MODEL
              ,so.serial_no                     SERIAL_NO
             -- ,TO_NUMBER(so.point,'999999999')  WT_AMT
              ,TO_NUMBER(so.amt,'999999999')    INCT_AMT
              ,pk_sms_mst.FN_SMS_TRANS(so.sellout_resp_msg)             
                                                MSG_BODY
              ,t.scheme_name                    SCHEME
        FROM   TB_SN_SO_WT_MST                  SO         
              ,TB_CM_REGION                     R
              ,TB_CM_PROVINCE                   P
              ,TB_CM_SHOP_INF                   S
              ,TB_CM_BILLTO_INF                 B
              ,TB_CM_MODEL_CAT                  M
              ,TB_CM_INCENTIVE_MST              T
              ,(SELECT distinct(billto_name)
                      ,billto_code 
                      ,serial_no
                      ,model
                FROM  VW_SN_BILLTO  )           BT
        WHERE  s.reg_id                       = r.id
        AND    s.province_code                = p.id
        AND    s.shop_code                    = so.shop_code
        AND    s.billto_code                  = b.billto_code(+)
        AND    so.model                       = m.model(+)
        AND    so.model                       = bt.model(+)
        AND    so.serial_no                   = bt.serial_no(+)
        AND    so.incentive_cfm_period        = t.scheme_id(+)
        AND    TO_CHAR(so.sellout_date,'YYYYMMDD')                
                                       BETWEEN  v_s_date AND v_e_date
        AND    r.id                        LIKE v_region_id
        AND    so.close_flag                  = 'Y'
        AND    s.shop_code                 LIKE DECODE(p_shop_code, null, '%', '%'||UPPER(p_shop_code)||'%')
        AND    UPPER(s.shop_name)          LIKE DECODE(p_shop_name, null, '%', '%'||UPPER(p_shop_name)||'%')
        /*GROUP BY
               r.name
              ,s.shop_code
              ,s.shop_name
              ,p.name
              ,s.address
              ,b.billto_code
              ,b.short_name
              ,m.prod_l1
              ,so.model
              ,t.scheme_name */
        ORDER BY
               r.name
              ,p.name 
              ,s.shop_code 
              ,s.shop_name
              ,m.prod_l1
              ,so.model
              ,so.serial_no;
    END IF;       
  END PR_INCT_CLOSED_INQ;
    
  -----------------------------------------------------------------------------------------------------
  --                           FUNCTION CREATE INCENTIVE SCHEME
  -----------------------------------------------------------------------------------------------------
  FUNCTION FN_INCT_SCHEME_CRT(            
           p_region             VARCHAR2
          ,p_scheme_name        VARCHAR2
          ,p_purpose            VARCHAR2
          ,p_start_date         VARCHAR2
          ,p_end_date           VARCHAR2
          ,p_create_user        VARCHAR2)
  RETURN                        VARCHAR2 
  -----------------------------------------------------------------------------------------------------
  IS                            PRAGMA AUTONOMOUS_TRANSACTION;
    v_scheme_id                 VARCHAR2(12) := p_region || SUBSTR(p_start_date,3,6) ||SUBSTR(p_end_date,5,4) ;
    v_start_date                DATE         := TO_DATE(p_start_date, 'YYYYMMDD');
    v_end_date                  DATE         := TO_DATE(p_end_date,   'YYYYMMDD');
  -----------------------------------------------------------------------------------------------------
  BEGIN                                        
    INSERT INTO                 TB_CM_INCENTIVE_MST( 
                                scheme_id            ,scheme_name
                               ,start_date           ,end_date
                               ,purpose              ,create_date
                               ,create_user          ,update_date
                               ,region)
    VALUES                      (
                                v_scheme_id          ,p_scheme_name
                               ,v_start_date         ,v_end_date
                               ,p_purpose            ,sysdate
                               ,p_create_user        ,sysdate
                               ,p_region);
    COMMIT;
      
    RETURN                      'DONE';               
  EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
      RETURN                    'DUPLICATED';
         
      ROLLBACK;                                                                    
  END FN_INCT_SCHEME_CRT;   
  -----------------------------------------------------------------------------------------------------
  --                           PROCEDURE INCENTIVE SCHEME INQUIRY
  -----------------------------------------------------------------------------------------------------
  PROCEDURE PR_INCT_SCHEME_INQ(
            out_cur            OUT               SYS_REFCURSOR
           ,p_scheme_id        IN                VARCHAR2 DEFAULT null
           ,p_scheme_name      IN                VARCHAR2 DEFAULT null
           ,p_create_mm        IN                VARCHAR2 DEFAULT null
           ,p_user_region      IN                VARCHAR2 DEFAULT '00'
           ,p_region_id        IN                VARCHAR2 DEFAULT '%')
  -----------------------------------------------------------------------------------------------------
  IS
    v_region_id                                  VARCHAR2(02) := null;
  -----------------------------------------------------------------------------------------------------
  BEGIN
    IF p_user_region                           = '00' THEN
      v_region_id                             := p_region_id;
    ELSE
      v_region_id                             := p_user_region;
    END IF;    
    OPEN out_cur FOR 
      SELECT r.name                              REGION 
            ,c.scheme_name                       SCHEME
            ,c.scheme_id                         ID
            ,TO_CHAR(c.start_date,'YYYY/MM/DD')  START_DATE
            ,TO_CHAR(c.end_date,'YYYY/MM/DD')    END_DATE
            ,TO_CHAR(c.create_date,'YYYY/MM/DD') CREATE_DATE
            ,c.create_user                       CREATE_USER
            ,c.purpose                           PURPOSE 
      FROM   TB_CM_INCENTIVE_MST                 C
            ,TB_CM_REGION                        R
      WHERE  c.region                          = r.id
      AND    c.region                       LIKE v_region_id
      AND    c.scheme_name                  LIKE DECODE(p_scheme_name, null, '%','%'||UPPER(p_scheme_name)||'%')
      AND    c.scheme_id                    LIKE DECODE(p_scheme_id,   null, '%','%'||p_scheme_id         ||'%')
      AND    TO_CHAR(c.create_date,'YYYYMM') LIKE DECODE(p_create_mm,  null, '%','%'||p_create_mm         ||'%');
  END PR_INCT_SCHEME_INQ;             
  -----------------------------------------------------------------------------------------------------
  --                           FUNCTION VALIDATE INCENTIVE UPLOAD MODELs
  -----------------------------------------------------------------------------------------------------
  FUNCTION FN_INCT_MD_UPL_VAL(            
            p_scheme_id         VARCHAR2
           ,p_model             VARCHAR2
           ,p_point             VARCHAR2
           ,p_amt               VARCHAR2)
  RETURN                        VARCHAR2
  -----------------------------------------------------------------------------------------------------
  IS
    v_scheme_id                 VARCHAR2(12)  := null;
    v_model                     VARCHAR2(20)  := null;
    v_point_ck                  NUMBER  (10)  := 0   ;
    v_amt_ck                    NUMBER  (10)  := 0   ;
    v_return                    VARCHAR2(100) := null;
  -----------------------------------------------------------------------------------------------------
  BEGIN
    --Checking available incentive scheme
    BEGIN
      SELECT                    scheme_id 
      INTO                      v_scheme_id
      FROM                      TB_CM_INCENTIVE_MST
      WHERE  scheme_id        = p_scheme_id;
      
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        v_return             := 'Ko co incentive scheme/';
    END;
    --Validating model info
    BEGIN
      SELECT                    model
      INTO                      v_model
      FROM                      TB_CM_MODEL_CAT
      WHERE  model            = UPPER(p_model);
        
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        v_return             := v_return || 'Ko co model nay/';
    END;
    --Validating incentive's model amt/point
    BEGIN
      SELECT                    TO_NUMBER(p_point)
                               ,TO_NUMBER(p_amt)
      INTO                      v_point_ck
                               ,v_amt_ck
      FROM                      DUAL;
    EXCEPTION
      WHEN OTHERS THEN
        v_return             := v_return || 'Point hoac amt ko dung dinh dang so/';              
    END;    
    IF v_amt_ck              < 0 
                             OR v_amt_ck IS NULL THEN
       v_return              := v_return || 'Amount can phai >0/';
    END IF;  
    --Return the validation
    IF v_return              IS NULL THEN
      v_return               := 'DONE';
    END IF;
        
    RETURN                      v_return;
  EXCEPTION
    WHEN OTHERS THEN RETURN SQLERRM;  
  END FN_INCT_MD_UPL_VAL;
  -----------------------------------------------------------------------------------------------------
  --                           FUNCTION INCENTIVE MODEL UPLOAD SAVE               
  -----------------------------------------------------------------------------------------------------    
  FUNCTION FN_INCT_MD_UPL_SV(        
           p_scheme_id          VARCHAR2
          ,p_model              VARCHAR2
          ,p_point              VARCHAR2
          ,p_amt                VARCHAR2
          ,p_upload_user        VARCHAR2)
  RETURN                        VARCHAR2
  -----------------------------------------------------------------------------------------------------
  IS                            PRAGMA AUTONOMOUS_TRANSACTION;
    v_region                    VARCHAR2(02) := SUBSTR(p_scheme_id, 1,2);
  -----------------------------------------------------------------------------------------------------
  BEGIN
    INSERT INTO                 TB_CM_INCENTIVE_DETAIL(
                                region                  ,scheme_id
                               ,model                   ,point
                               ,amt                     ,create_user)
    VALUES                      (
                                v_region                ,p_scheme_id
                               ,p_model                 ,p_point
                               ,p_amt                   ,p_upload_user);
    COMMIT;
      
    RETURN                      'DONE';
  -----------------------------------------------------------------------------------------------------
  EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
      RETURN                    'DUPLICATED';   
        
      ROLLBACK;
    WHEN OTHERS THEN RETURN SQLERRM;                                 
  END FN_INCT_MD_UPL_SV;  
  -----------------------------------------------------------------------------------------------------
  --                           PRCEDURE INCECTIVE MODEL INQUIRY
  -----------------------------------------------------------------------------------------------------
  PROCEDURE PR_INCT_MD_INQ(
            out_cur              OUT        SYS_REFCURSOR
           ,p_scheme_id          IN         VARCHAR2
           ,p_model              IN         VARCHAR2 DEFAULT null)
  IS
  BEGIN
    OPEN out_cur FOR
      SELECT r.name                         REGION
            ,m.prod_l1                      PRODUCT
            ,i.model                        MODEL
            ,TO_CHAR(i.point,'999,999,999') POINT
            ,TO_CHAR(i.amt,'999,999,999')   AMT
      FROM   TB_CM_INCENTIVE_DETAIL         I
            ,TB_CM_MODEL_CAT                M
            ,TB_CM_REGION                   R
      WHERE  i.model                      = m.model
      AND    i.region                     = r.id 
      AND    i.scheme_id                  = p_scheme_id
      AND    i.model                   LIKE DECODE(p_model, null, '%','%'||UPPER(p_model)||'%')
      ORDER BY
             r.name
            ,m.prod_l1
            ,i.model;        
  END PR_INCT_MD_INQ;            
  -----------------------------------------------------------------------------------------------------
  --                           FUNCTION SELL_OUT INCENTIVE VALIDATION
  -----------------------------------------------------------------------------------------------------
  FUNCTION FN_SO_INCT_VAL(   
           p_shop_code          VARCHAR2
          ,p_model              VARCHAR2
          ,p_serial_no          VARCHAR2
          ,p_sellout_date       VARCHAR2)
  RETURN                        INCT_STATITICS
  -----------------------------------------------------------------------------------------------------
  IS
           v_inct_region        VARCHAR2(02)   := null;
           v_inct_scheme        VARCHAR2(12)   := null;
           v_inct_start_date    VARCHAR2(08)   := null;
           v_inct_end_date      VARCHAR2(08)   := null;
           v_inct_point         VARCHAR2(12)   := null;
           v_inct_amt           VARCHAR2(12)   := null;
           v_inct_adhoc_amt     VARCHAR2(12)   := 0;
           v_inct_adhoc_tem_amt VARCHAR2(12)   := null;
           v_province_code      VARCHAR2(02)   := null;
           
           v_prod_l1            VARCHAR2(02)   := null;
           v_bill_to            VARCHAR2(20)   := null;
           
         --  v_model_amt          VARCHAR2(12)   := 0;
           
           v_return             INCT_STATITICS := INCT_STATITICS(null, null, null, null, null, null);
  -----------------------------------------------------------------------------------------------------
  BEGIN
    SELECT distinct(LTRIM(RTRIM((prod_l1))))
    INTO   v_prod_l1
    FROM   TB_CM_MODEL_CAT
    WHERE  model           = p_model
    GROUP  BY   prod_l1;
    
    BEGIN
      --Validating the sellout scheme
      SELECT                    region
                               ,scheme_id
                               ,TO_CHAR(start_date,'YYYYMMDD')
                               ,TO_CHAR(end_date, 'YYYYMMDD')
      INTO                      v_inct_region
                               ,v_inct_scheme
                               ,v_inct_start_date
                               ,v_inct_end_date
      FROM                      TB_CM_INCENTIVE_MST i
      WHERE TO_CHAR(start_date,'YYYYMMDD') <= p_sellout_date
      AND   TO_CHAR(end_date,'YYYYMMDD')   >= p_sellout_date
      AND   i.close_flag                    = 'N'
      AND   i.mdi                           = 'T';
      
     --CASE : Incentive scheme is applied to separated region
      IF   v_inct_region    != '00' THEN
        BEGIN
          SELECT                region
                               ,scheme_id
                               ,TO_CHAR(start_date,'YYYYMMDD')
                               ,TO_CHAR(end_date, 'YYYYMMDD')
          INTO                  v_inct_region
                               ,v_inct_scheme
                               ,v_inct_start_date
                               ,v_inct_end_date
          FROM                  TB_CM_INCENTIVE_MST i
          WHERE   region     = (SELECT                         reg_id
                                FROM                           TB_CM_SHOP_INF S
                                WHERE  shop_code             = p_shop_code)                         
          AND     TO_CHAR(start_date,'YYYYMMDD')            <= p_sellout_date
          AND     TO_CHAR(end_date,'YYYYMMDD')              >= p_sellout_date;
                     
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
           IF v_prod_l1 IN ('AC') THEN
             v_return.inct_type:= const_inct_out_period;
             v_return.inct_msg := '$'|| p_model || ', Serial ' || p_serial_no;
              
             RETURN                v_return;
           END IF;    
        END;
      END IF;
      
    --CASE : Scheme is not available for sell-out period
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        IF v_prod_l1 IN ('AC') THEN
          v_return.inct_type:= const_inct_out_period;
          v_return.inct_msg := '$'|| p_model || ', Serial ' || p_serial_no;
            
          RETURN               v_return;
        END IF;
     --CASE : Incentive scheme is applied to separated region
      WHEN TOO_MANY_ROWS THEN
       BEGIN
         SELECT                region
                              ,scheme_id
                              ,TO_CHAR(start_date,'YYYYMMDD')
                              ,TO_CHAR(end_date, 'YYYYMMDD')
         INTO                  v_inct_region
                              ,v_inct_scheme
                              ,v_inct_start_date
                              ,v_inct_end_date
         FROM                  TB_CM_INCENTIVE_MST      I
         WHERE   region     = (SELECT                         reg_id
                               FROM                           TB_CM_SHOP_INF
                               WHERE  shop_code             = p_shop_code)                         
         AND     TO_CHAR(start_date,'YYYYMMDD')            <= p_sellout_date
         AND     TO_CHAR(end_date,'YYYYMMDD')              >= p_sellout_date;
            
       EXCEPTION
         WHEN NO_DATA_FOUND THEN
          IF v_prod_l1 IN ('AC') THEN
            v_return.inct_type:= const_inct_out_period;
            v_return.inct_msg := '$'|| p_model || ', Serial ' || p_serial_no;
               
            RETURN              v_return; 
          END IF;   
       END;   
    END;
    --Validating model is available for the sell-out
    BEGIN
      SELECT                    point
                               ,amt
      INTO                      v_inct_point
                               ,v_inct_amt       
      FROM                      TB_CM_INCENTIVE_DETAIL 
      WHERE   region          = v_inct_region
      AND     scheme_id       = v_inct_scheme
      AND     model           = p_model
      AND     TO_CHAR(create_date,'YYYYMMDD') = (SELECT MAX(TO_CHAR(create_date,'YYYYMMDD'))
                                                 FROM                      TB_CM_INCENTIVE_DETAIL
                                                 WHERE   region          = v_inct_region
                                                 AND     scheme_id       = v_inct_scheme
                                                 AND     model           = p_model);
      
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        --IF v_prod_l1 IN ('AC') THEN
          v_return.inct_type:= const_inct_out_model;
          v_return.inct_msg := '$'|| p_model || 
                               '$'|| p_serial_no; --'$'|| p_model || ', Serial ' || p_serial_no;
          
       /* ELSE
          v_return.inct_region:= '00';
          v_return.inct_scheme:= v_inct_scheme;
          v_return.inct_point := 0; -- Before 2014's Jan 28th 10000;
          v_return.inct_amt   := 0;
          v_return.inct_type  := const_wt_inct_valid;
          v_return.inct_msg   := '$'|| p_model || 
                                 '$'|| p_serial_no ||
                                 '$'|| TO_CHAR(sysdate,'DD/MM/YYYY') ||
                                 '$'|| '0';   
        END IF;*/
        
        RETURN               v_return;     
    END;
    
    --Settle the incentive
  --  IF v_prod_l1 NOT IN ('AC') THEN
    --Filtering Bill_to for MC 
      
    BEGIN
      SELECT DISTINCT(billto_code)
      INTO   v_bill_to
      FROM   VW_SN_BILLTO
      WHERE  serial_no     = p_serial_no
      AND    model         = p_model
      AND    out_date      = (SELECT MAX(out_date)
                              FROM   VW_SN_BILLTO
                              WHERE  serial_no     = p_serial_no
                              AND    model         = p_model)
      AND   update_date    = (SELECT MAX(update_date)
                              FROM   VW_SN_BILLTO
                              WHERE  serial_no     = p_serial_no
                              AND    model         = p_model);
     /* AND    billto_code NOT IN (SELECT billto_code
                                 FROM   TB_NON_INCENTIVE_CUST);*/
      BEGIN
        SELECT DISTINCT(billto_code)
        INTO   v_bill_to
        FROM   TB_CM_SHOP_BILLTO
        WHERE  billto_code   = v_bill_to
        AND    shop_code     = p_shop_code;
                         
          IF v_bill_to NOT IN ('VN006566001B','VN006603001B','VN006646001B','VN006784001B','VN007759001B'
                              ,'VN002157001B','VN002272001B','VN002386001B','VN002621001B','VN004878001B'
                              ,'VN005984001B','VN006507001B','VN006609001B','VN006610001B','VN007027001B'
                              ,'VN007116001B','VN007724001B','VN007752001B','VN007808001B','VN007820001B'
                              ,'VN002346001B','VN006755001B','VN006782001B') THEN  
              
            FOR c1 IN (SELECT scheme_id
                             ,TO_CHAR(start_date,'YYYYMMDD')
                             ,TO_CHAR(end_date,'YYYYMMDD')
                             ,region    
                       FROM TB_CM_INCENTIVE_ADHOC
                       WHERE close_flag ='N'
                       AND   TO_CHAR(start_date,'YYYYMMDD') <= p_sellout_date
                       AND   TO_CHAR(end_date  ,'YYYYMMDD') >= p_sellout_date
                       AND   purpose_type                    = 'S')
            LOOP
              BEGIN 
                SELECT DISTINCT(amt)
                INTO   v_inct_adhoc_amt
                FROM   TB_CM_INCENTIVE_ADHOC_DETAIL
                WHERE  scheme_id   = c1.scheme_id
                AND    billto_code = v_bill_to
                AND    model       = p_model
                AND    region      = c1.region;
                
                BEGIN
                  SELECT DISTINCT(s.province_code)
                  INTO   v_province_code 
                  FROM   tb_cm_shop_inf s
                        ,tb_cm_province p
                  WHERE  s.province_code = p.id
                  AND    s.shop_code     = p_shop_code      
                  AND    p.id IN ('20','27','12','3','7','25','54','28','40','26','1');
                 
                  v_inct_amt        := TO_CHAR(TO_NUMBER(v_inct_amt) + TO_NUMBER(v_inct_adhoc_amt));
                EXCEPTION
                  WHEN NO_DATA_FOUND THEN null;
                  WHEN OTHERS THEN  
                    /*v_return.inct_msg   := '$'|| p_model || 
                                   '$'|| p_serial_no ||
                                   '$'|| TO_CHAR(sysdate,'DD/MM/YYYY') ||
                                   '$'|| LTRIM(TO_CHAR(TO_NUMBER(v_inct_amt),'999G999','NLS_NUMERIC_CHARACTERS = '',.'));*/
                    
                    PR_RAISE_ERR( 'PK_SOM_MST.FN_SO_INCT_VAL.ADHOC' , DBMS_UTILITY.format_error_backtrace || '/'|| SQLERRM 
                                          ,p_shop_code ||'/'|| p_serial_no ||'/' || p_model
                                                           || ':'     || TO_CHAR(sysdate, 'YYYY/MM/DD HH24:MI:SS')); 
                   NULL;                                                      
                END; 
              EXCEPTION
                WHEN NO_DATA_FOUND THEN null;
              END;             
            END LOOP;         
            
            v_return.inct_msg   := '$'|| p_model || 
                                   '$'|| p_serial_no ||
                                   '$'|| TO_CHAR(sysdate,'DD/MM/YYYY') ||
                                   '$'|| LTRIM(TO_CHAR(TO_NUMBER(v_inct_amt),'999G999','NLS_NUMERIC_CHARACTERS = '',.'));
                                      /*'$'|| p_model || ', Serial ' || p_serial_no ||
                                     '$'|| TO_CHAR(sysdate,'DD/MM/YYYY') ||
                                     '$'|| LTRIM(TO_CHAR(TO_NUMBER(v_inct_amt)-10000,'999G999','NLS_NUMERIC_CHARACTERS = '',.'));*/
            /*ELSE
              v_return.inct_point := 0;
              v_return.inct_msg   := '$'|| p_model ||
                                     '$'|| p_serial_no ||
                                     '$'|| TO_CHAR(sysdate,'DD/MM/YYYY') ||
                                     '$'|| LTRIM(TO_CHAR(TO_NUMBER(v_inct_amt),'999G999','NLS_NUMERIC_CHARACTERS = '',.'));*/
                                     /*'$'|| p_model || ', Serial ' || p_serial_no ||
                                     '$'|| TO_CHAR(sysdate,'DD/MM/YYYY') ||
                                     '$'|| LTRIM(TO_CHAR(TO_NUMBER(v_inct_amt),'999G999','NLS_NUMERIC_CHARACTERS = '',.'));*/
           -- END IF;
          ---NOT IN REGISTERED BILLTOs----------------------
            v_return.inct_amt   := v_inct_amt;
            v_return.inct_region:= v_inct_region;
            v_return.inct_scheme:= v_inct_scheme;
            v_return.inct_point := 0; --Before 2014 Jan 28 = 10000;
            v_return.inct_type  := const_wt_inct_valid;
          ELSE
            v_return.inct_type  := const_inct_wr_channel;
            v_return.inct_point := 0;
            v_return.inct_amt   := 0;
            v_return.inct_msg   := '$'|| p_model || 
                                   '$'|| p_serial_no;
                                     
                                   /*'$'|| p_model || ', Serial ' || p_serial_no ; \*||
                                   '$'|| TO_CHAR(sysdate,'DD/MM/YYYY') ||
                                   '$'|| '0';*\*/
          END IF;
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
            v_return.inct_type  := const_inct_wr_channel;
            v_return.inct_point := 0;
            v_return.inct_amt   := 0;
            v_return.inct_msg   := '$'|| p_model || 
                                   '$'|| p_serial_no ;
            
          WHEN OTHERS THEN
            PR_RAISE_ERR( 'PK_SOM_MST.FN_SO_INCT_VAL' , DBMS_UTILITY.format_error_backtrace || '/'|| SQLERRM 
                                          ,p_shop_code ||'/'|| p_serial_no ||'/' || p_model
                                                           || ':'     || TO_CHAR(sysdate, 'YYYY/MM/DD HH24:MI:SS')); 
        END;  
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        /*v_return.inct_msg   := '$'|| p_model || ', Serial ' || p_serial_no ||
                               '$'|| TO_CHAR(sysdate,'DD/MM/YYYY') ||
                               '$'|| '0';
        v_return.inct_amt   := 0; */
        v_return.inct_type  := const_inct_wr_channel;
        v_return.inct_point := 0;
        v_return.inct_amt   := 0;
        v_return.inct_msg   := '$'|| p_model || 
                               '$'|| p_serial_no ;
              
       /* v_return.inct_msg   := '$'|| p_model || ', Serial ' || p_serial_no ||
                               '$'|| TO_CHAR(sysdate,'DD/MM/YYYY') ||
                               '$'|| LTRIM(TO_CHAR(TO_NUMBER(v_inct_amt)-10000,'999G999','NLS_NUMERIC_CHARACTERS = '',.'));*/
      WHEN OTHERS THEN
        PR_RAISE_ERR( 'PK_SOM_MST.FN_SO_INCT_VAL' , DBMS_UTILITY.format_error_backtrace || '/'|| SQLERRM 
                                          ,p_shop_code ||'/'|| p_serial_no ||'/' || p_model
                                                           || ':'     || TO_CHAR(sysdate, 'YYYY/MM/DD HH24:MI:SS'));
    END;                            
    /*ELSE
      v_return.inct_region:= v_inct_region;
      v_return.inct_scheme:= v_inct_scheme;
      v_return.inct_point := v_inct_point;
      v_return.inct_amt   := v_inct_amt;
      v_return.inct_type  := const_inct_valid;
      v_return.inct_msg   := '$'|| p_model || ', Serial ' || p_serial_no ||
                             '$'|| TO_CHAR(sysdate,'DD/MM/YYYY') ||
                             '$'|| LTRIM(TO_CHAR(v_inct_amt,'999G999','NLS_NUMERIC_CHARACTERS = '',.')); 
                              
    END IF;   */
    
    
    RETURN                   v_return;
  EXCEPTION
    WHEN OTHERS THEN
      PR_RAISE_ERR( 'PK_SOM_MST.FN_SO_INCT_VAL' , DBMS_UTILITY.format_error_backtrace || '/'|| SQLERRM 
                                          ,p_shop_code ||'/'|| p_serial_no ||'/' || p_model
                                                           || ':'     || TO_CHAR(sysdate, 'YYYY/MM/DD HH24:MI:SS'));
      /*dbms_output.put_line ('PK_SOM_MST.FN_SO_INCT_VAL  :' || SQLERRM || '=>' || p_shop_code ||'/'|| p_serial_no ||'/' || p_model
                                                           || ':'     || TO_CHAR(sysdate, 'YYYY/MM/DD HH24:MI:SS'));
      write_log;*/
      RETURN                    v_return;        
  END FN_SO_INCT_VAL;
  -----------------------------------------------------------------------------------------------------
  --                           FUNCTION SELL_OUT DEALER INCENTIVE VALIDATION
  -----------------------------------------------------------------------------------------------------
  FUNCTION FN_SO_INCT_DEALER_VAL(   
           p_shop_code          VARCHAR2
          ,p_model              VARCHAR2
          ,p_serial_no          VARCHAR2
          ,p_sellout_date       VARCHAR2)
  RETURN                        INCT_STATITICS
  -----------------------------------------------------------------------------------------------------
  IS
           v_inct_region        VARCHAR2(02)   := null;
           v_inct_scheme        VARCHAR2(12)   := null;
           v_inct_start_date    VARCHAR2(08)   := null;
           v_inct_end_date      VARCHAR2(08)   := null;
           v_inct_point         VARCHAR2(12)   := null;
           v_inct_amt           VARCHAR2(12)   := null;
      
           v_return             INCT_STATITICS := INCT_STATITICS(null, null, null, null, null, null);
  BEGIN
    BEGIN
    --Validating the sellout scheme
      SELECT                    region
                               ,scheme_id
                               ,TO_CHAR(start_date,'YYYYMMDD')
                               ,TO_CHAR(end_date, 'YYYYMMDD')
      INTO                      v_inct_region
                               ,v_inct_scheme
                               ,v_inct_start_date
                               ,v_inct_end_date
      FROM                      TB_CM_INCENTIVE_MST i
      WHERE TO_CHAR(start_date,'YYYYMMDD') <= p_sellout_date
      AND   TO_CHAR(end_date,'YYYYMMDD')   >= p_sellout_date
      AND   i.close_flag                    = 'N'
      AND   i.mdi                           = 'N'
      AND   i.scheme_name                LIKE p_shop_code ||'%'  
      AND   i.region                        = (SELECT     reg_id
                                               FROM       TB_CM_SHOP_INF S
                                               WHERE      shop_code      = p_shop_code);
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        v_return.inct_type:= const_inct_out_period;
        v_return.inct_msg := '$'|| p_model || ', Serial ' || p_serial_no;
        
        RETURN               v_return;
    END;
    BEGIN
      --Validating model is available for the sell-out
      SELECT                    point
                               ,amt
      INTO                      v_inct_point
                               ,v_inct_amt       
      FROM                      TB_CM_INCENTIVE_DETAIL 
      WHERE   region          = v_inct_region
      AND     scheme_id       = v_inct_scheme
      AND     model           = p_model;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        v_return.inct_type:= const_inct_out_model;
        v_return.inct_msg := '$'|| p_model || ', Serial ' || p_serial_no;
           
        RETURN               v_return;  
    END;
    --Gathering the incentive info
    v_return.inct_region:= v_inct_region;
    v_return.inct_scheme:= v_inct_scheme;
    v_return.inct_point := v_inct_point;
    v_return.inct_amt   := v_inct_amt;
    v_return.inct_type  := const_inct_valid;
    v_return.inct_msg   := '$'|| p_model || ', Serial ' || p_serial_no ||
                           '$'|| TO_CHAR(sysdate,'DD/MM/YYYY') ||
                           '$'|| LTRIM(TO_CHAR(v_inct_amt,'999,999,999'));   
      
    RETURN                 v_return;                                                  
  END      FN_SO_INCT_DEALER_VAL;          
  -----------------------------------------------------------------------------------------------------
  --                           PROCEDURE SELL-OUT INCENTIVE AUTO CONFIRMATION
  -----------------------------------------------------------------------------------------------------
  PROCEDURE PR_SO_INCT_AUTO_CFM 
  -----------------------------------------------------------------------------------------------------
  IS
            v_shop_code                      VARCHAR2(10) := null;
            v_shop_cell                      VARCHAR2(20) := null;
            v_serial_no                      VARCHAR2(25) := null;
            v_model                          VARCHAR2(20) := null;
            v_suffix                         VARCHAR2(20) := null;
            v_sellout_date                   VARCHAR2(08);
            v_cfm_user                       VARCHAR2(10) := null;
            v_sms_yn                         VARCHAR2(01)  := null;
  -----------------------------------------------------------------------------------------------------
  BEGIN
    FOR curs1 IN( 
    SELECT  shop_code                        SHOP_CODE
           ,shop_cell                        SHOP_CELL
           ,serial_no                        SERIAL_NO
           ,LTRIM(RTRIM(model))              MODEL
           ,suffix                           SUFFIX
           ,TO_CHAR(sellout_date,'YYYYMMDD') SELLOUT_DATE
           ,incentive_cfm_user               INCENTIVE_CFM_USER 
           ,sms_yn                           SMS_YN
    FROM    TB_SN_SO_WT_MST
    WHERE   incentive_cfm_flag             = 'N'
    AND     close_flag                     = 'N'
    AND     sms_yn                         = 'Y')
    LOOP
            v_shop_code                   := curs1.shop_code;
            v_shop_cell                   := curs1.shop_cell;
            v_serial_no                   := curs1.serial_no;
            v_model                       := curs1.model;
            v_suffix                      := curs1.suffix;
            v_sellout_date                := curs1.sellout_date;
            v_cfm_user                    := 'SYS';
            v_sms_yn                      := curs1.sms_yn;
        
            --Confirming the incentive by set
            PR_SO_INCT_SET_CFM             ( v_shop_code ,v_shop_cell
                                            ,v_serial_no ,v_model
                                            ,v_suffix    ,v_sellout_date
                                            ,v_cfm_user  ,v_sms_yn);
                              
    END LOOP;
  -----------------------------------------------------------------------------------------------------  
  EXCEPTION  
    WHEN OTHERS THEN
      PR_RAISE_ERR
                 ('PK_SOM_MST.PR_SO_INCT_AUTO_CFM'
                 ,DBMS_UTILITY.format_error_backtrace || '/'||SQLERRM 
                 ,v_shop_code ||'/'|| v_serial_no ||'/' || v_model);
  END PR_SO_INCT_AUTO_CFM;    
  -----------------------------------------------------------------------------------------------------
  --                           PROCEDURE SELL-OUT INCENTIVE SET CONFIMATION
  ----------------------------------------------------------------------------------------------------- 
  PROCEDURE PR_SO_INCT_SET_CFM(    
            p_shop_code               VARCHAR2
           ,p_shop_cell               VARCHAR2
           ,p_serial_no               VARCHAR2
           ,p_model                   VARCHAR2
           ,p_suffix                  VARCHAR2
           ,p_sellout_date            VARCHAR2
           ,p_cfm_user                VARCHAR2
           ,p_sms_yn                  VARCHAR2)
  -----------------------------------------------------------------------------------------------------
  IS
            v_resp_type               VARCHAR2(10)   := null;
            v_resp_msg                VARCHAR2(160)  := null;
            v_inct_bal_ym             VARCHAR2(06)   := TO_CHAR(sysdate,'YYYYMM');
            v_inct_stat               INCT_STATITICS := INCT_STATITICS(null, null, null, null, null, null);
  -----------------------------------------------------------------------------------------------------
  BEGIN
            --Gathering incentive's statitics for sell-out
            v_inct_stat            := FN_SO_INCT_VAL( p_shop_code ,p_model 
                                                     ,p_serial_no ,p_sellout_date);
      
            v_resp_type            := v_inct_stat.inct_type;
            v_resp_msg             := pk_sms_mst.FN_GET_MT_RESPONSE( v_resp_type ,v_inct_stat.inct_msg);
            
    --CASE : Incenitve is not available for the sell-out                                               
    IF v_inct_stat.inct_scheme     IS NULL THEN
      UPDATE                          TB_SN_SO_WT_MST
      SET   incentive_cfm_flag      = 'Y'
           ,incentive_cfm_period    = 'NA'
           ,point                   = 0
           ,amt                     = 0
           ,incentive_cfm_date      = sysdate
           ,last_update_date        = sysdate
           ,incentive_cfm_user      = p_cfm_user
           ,sellout_resp_msg        = v_resp_msg
      WHERE shop_code               = p_shop_code
      AND   serial_no               = p_serial_no
      AND   model                   = p_model
      AND   suffix                  = p_suffix;
        
      COMMIT;
    --CASE : Incenitve is available for the sell-out
    ELSE
      UPDATE                          TB_SN_SO_WT_MST
      SET   incentive_cfm_flag      = 'Y'
           ,incentive_cfm_period    = v_inct_stat.inct_scheme
           ,point                   = v_inct_stat.inct_point
           ,amt                     = v_inct_stat.inct_amt
           ,incentive_cfm_date      = sysdate
           ,last_update_date        = sysdate
           ,incentive_cfm_user      = p_cfm_user
           ,sellout_resp_msg        = v_resp_msg
      WHERE shop_code               = p_shop_code
      AND   serial_no               = p_serial_no
      AND   model                   = p_model
      AND   suffix                  = p_suffix;
      
      COMMIT;
      --Saving history
      pk_sms_mst.PR_UPDATE_SN_SO_WT_HIST  ( p_serial_no             ,v_resp_type
                                           ,v_resp_msg              ,v_resp_msg 
                                           ,TO_DATE(p_sellout_date,'YYYYMMDD') ,p_shop_cell
                                           ,null                    ,'Y'                   
                                           ,'0'                     ,'LGDK' 
                                           ,p_model                 ,p_shop_code);   
                                    
      --Update Incentive balance
      
      PR_SO_INCT_BAL_SV             ( v_inct_stat.inct_region         ,v_inct_stat.inct_scheme
                                     ,p_shop_code                     ,p_model 
                                     ,p_serial_no                     ,TO_NUMBER(v_inct_stat.inct_point)
                                     ,TO_NUMBER(v_inct_stat.inct_amt) ,v_inct_bal_ym);
    END IF;
    --CASE : Reply a SMS if the registerd sell-out by SMS.                                 
    IF p_sms_yn                     = 'Y' THEN
      INSERT INTO                     TB_MT_HIST(
                                      moseq                       ,cell_no
                                     ,cmdcode                     ,resp_type
                                     ,msgbody                     ,mo_msgbody
                                     ,mt_seq)
      VALUES                         (
                                      '0'                         ,p_shop_cell
                                     ,pk_sms_mst.const_cmdcode_so ,v_resp_type
                                     ,v_resp_msg                  ,'INCENTIVE_CFM'
                                     ,mt_seq.nextval);
      COMMIT;
        
    END IF;
  -----------------------------------------------------------------------------------------------------  
  EXCEPTION  
    WHEN OTHERS THEN
      PR_RAISE_ERR 
                 ('PK_SOM_MST.PR_SO_INCT_SET_CFM',DBMS_UTILITY.format_error_backtrace || '/'||SQLERRM 
                                                 ,p_shop_code ||'/'|| p_serial_no ||'/' || p_model);
  END PR_SO_INCT_SET_CFM;
  -----------------------------------------------------------------------------------------------------
  --                           PROCEDURE SELL-OUT INCENTIVE MANUAL CONFIMATION
  ----------------------------------------------------------------------------------------------------- 
  /*PROCEDURE PR_SO_INCT_DEALER_SET_CFM(    
            p_shop_code               VARCHAR2
           ,p_shop_cell               VARCHAR2
           ,p_serial_no               VARCHAR2
           ,p_model                   VARCHAR2
           ,p_suffix                  VARCHAR2
           ,p_sellout_date            VARCHAR2
           ,p_cfm_user                VARCHAR2
           ,p_sms_yn                  VARCHAR2)
  IS
            v_inct_stat               INCT_STATITICS := INCT_STATITICS(null, null, null, null, null, null);
  BEGIN
            v_inct_stat.inct_region :='01';        
  END       PR_SO_INCT_DEALER_SET_CFM;      */   
  -----------------------------------------------------------------------------------------------------
  --                           PROCEDURE INCENTIVE DEALER MANUAL CONFIRMATION
  -----------------------------------------------------------------------------------------------------
  FUNCTION FN_SO_INCT_DEALER_SET_MNL_CFM(
            p_shop_code               VARCHAR2
           ,p_shop_cell               VARCHAR2 
           ,p_edi_no                  VARCHAR2
           ,p_serial_no               VARCHAR2
           ,p_model                   VARCHAR2
           ,p_suffix                  VARCHAR2
           ,p_sellout_date            VARCHAR2
           ,p_sellin_date             VARCHAR2
           ,p_user_cell               VARCHAR2
           ,p_cfm_comment             VARCHAR2
           ,p_cfm_user                VARCHAR2)
  RETURN                              VARCHAR2
  -----------------------------------------------------------------------------------------------------
  IS                                  PRAGMA AUTONOMOUS_TRANSACTION;
            v_wt_resp_type            VARCHAR2(020) := null;
            v_wt_resp_msg             VARCHAR2(200) := null;
            v_wt_resp                 WT_RESP_TYPE  := WT_RESP_TYPE(null, null, null);
  -----------------------------------------------------------------------------------------------------        
  BEGIN
    v_wt_resp_type                    := pk_sms_mst.const_wt_sn_vl;
    --UPDATE SELLOUT MASTER
    pk_sms_mst.PR_UPDATE_SN_SO_WT_MST   (p_shop_code              ,p_shop_cell     
                                        ,p_serial_no              ,p_model       
                                        ,p_suffix                 ,TO_DATE(p_sellin_date,'YYYYMMDD')      
                                        ,TO_DATE(p_sellout_date,'YYYYMMDD') ,TO_CHAR(sysdate,'HH24MISS') 
                                        ,v_wt_resp_type           ,v_wt_resp_msg
                                        ,'N'                      ,p_user_cell
                                        ,'Y'                      ,'N'); 
    v_wt_resp                         := pk_sms_mst.FN_GET_WT_RESPONSE(p_serial_no ,p_model);
    IF v_wt_resp.wt_resp_flag         = 'O' THEN
      IF v_wt_resp.wt_resp_year = 1 THEN
        v_wt_resp_type          := const_wt_12_vl;
      ELSIF v_wt_resp.wt_resp_year = 2 THEN
        v_wt_resp_type          := const_wt_24_vl;    
      END IF;  
    END IF;     
    v_wt_resp_msg                     := pk_sms_mst.FN_GET_MT_RESPONSE( v_wt_resp_type, v_wt_resp.wt_resp_msg);
    --UPDATE HISTTORY
    pk_sms_mst.PR_UPDATE_SN_SO_WT_HIST  (p_serial_no              ,v_wt_resp_type 
                                        ,v_wt_resp_msg            ,'NA' 
                                        ,TO_DATE(p_sellout_date,'YYYYMMDD') ,p_shop_cell 
                                        ,p_user_cell              ,'Y'                     
                                        ,p_edi_no                 ,pk_sms_mst.const_cmdcode_wt
                                        ,p_model                  ,p_shop_code             
                                        ,'Y');
    --BUDGE TO MT
    INSERT INTO                          TB_MT_HIST(
                                         moseq                    ,cell_no
                                        ,cmdcode                  ,resp_type
                                        ,msgbody                  ,mo_msgbody
                                        ,mo_receive_time          ,mt_seq)
    VALUES                              (      
                                         '0'                      ,p_user_cell
                                        ,pk_sms_mst.const_cmdcode_wt   ,v_wt_resp_type
                                        ,v_wt_resp_msg            ,p_shop_code || ' EDI'
                                        ,TO_DATE(p_sellout_date,'YYYYMMDD') ,mt_seq.nextval);
    COMMIT;
    
    UPDATE                               TB_SN_SO_DEALERS
    SET                                  cfm_flag         = 'Y'
                                        ,cfm_user         = p_cfm_user
                                        ,cfm_comment      = p_cfm_comment
                                        ,cfm_date         = sysdate
    WHERE                                edi_no           = p_edi_no;
    
    UPDATE tb_sn_rdc_hist
    SET    sellout_status     = 'Y'
    WHERE  serial_no          = p_serial_no
    AND    model              = p_model
    AND    suffix             = p_suffix;
              
    UPDATE tb_sn_cdc_hist
    SET    sellout_flag       = 'Y'
    WHERE  serial_no          = p_serial_no
    AND    model              = p_model
    AND    suffix             = p_suffix;
    
    COMMIT; 
          
    RETURN                               'DONE';
  EXCEPTION
    WHEN OTHERS THEN RETURN SQLERRM;                                       
  END       FN_SO_INCT_DEALER_SET_MNL_CFM;   
  -----------------------------------------------------------------------------------------------------
  --                           PROCEDURE INCENTIVE BALANCE UPDATE
  -----------------------------------------------------------------------------------------------------
  PROCEDURE PR_SO_INCT_BAL_SV(
            p_region                         VARCHAR2
           ,p_scheme                         VARCHAR2
           ,p_shop_code                      VARCHAR2
           ,p_model                          VARCHAR2
           ,p_serial_no                      VARCHAR2
           ,p_point                          NUMBER
           ,p_amt                            NUMBER
           ,p_yyyymm                         VARCHAR2)
  -----------------------------------------------------------------------------------------------------
  IS
            v_billto_code                    VARCHAR2(50) := null;
            v_billto_name                    VARCHAR2(50) := null;
            v_inct_bal_ck                    VARCHAR2(01)   := null;
  BEGIN          
    SELECT  si.billto_code                   BILLTO_CODE
           ,si.billto_name                   BILLTO_NAME
    INTO    v_billto_code
           ,v_billto_name
    FROM   (
            SELECT o1.inv_org                INV_ORG
                  ,o1.billto_code            BILLTO_CODE
                  ,o1.billto_name            BILLTO_NAME
                  ,o1.order_no               ORDER_NO
                  ,o1.pick_no                PICK_NO
                  ,o1.model                  MODEL 
                  ,c.serial_no               SERIAL_NO 
            FROM   TB_SN_CDC_HIST            C
                  ,TB_ORDER_SHIP_HIST        O1
            WHERE  c.inv_org(+)            = o1.inv_org
            AND    c.order_no(+)           = o1.order_no
            AND    c.model(+)              = o1.model
            AND    SUBSTR(c.serial_no,1,3)>= '209'
            AND    sellout_flag            = 'Y'  
          UNION
            SELECT o.inv_org                 INV_ORG
                  ,o.billto_code             BILLTO_CODE
                  ,o.billto_name             BILLTO_NAME
                  ,o.order_no                ORDER_NO
                  ,o.pick_no                 PICK_NO
                  ,o.model                   MODEL 
                  ,r.serial_no               SERIAL_NO
            FROM   TB_SN_RDC_HIST            R
                  ,TB_ORDER_SHIP_HIST        O
            WHERE  r.inv_org(+)            = o.inv_org 
            AND   (r.ship_no               = o.order_no OR r.edi_no             = o.pick_no)
            --AND    r.edi_no(+)             = o.pick_no
            AND    r.model(+)              = o.model
            AND    SUBSTR(r.serial_no,1,3)>= '209'
            AND    sellout_status          = 'Y' )
                                             SI
    WHERE   si.serial_no                   = p_serial_no
    AND     si.model                       = p_model;
    
    --Update Incentive balance
    BEGIN
      SELECT                                 'Y'
      INTO                                   v_inct_bal_ck
      FROM                                   TB_CM_INCENTIVE_BALANCE
      WHERE  region_id                     = p_region
      AND    scheme_id                     = p_scheme
      AND    billto_code                   = v_billto_code
      AND    shop_code                     = p_shop_code
      AND    model                         = p_model
      AND    yyyymm                        = p_yyyymm;
        
      UPDATE                                 TB_CM_INCENTIVE_BALANCE
      SET     point                        = TO_NUMBER(point) + p_point
             ,amt                          = TO_NUMBER(amt)   + p_amt
             ,qty                          = TO_NUMBER(qty)   + 1
             ,update_date                  = sysdate
      WHERE  region_id                     = p_region
      AND    scheme_id                     = p_scheme
      AND    billto_code                   = v_billto_code
      AND    shop_code                     = p_shop_code
      AND    model                         = p_model
      AND    yyyymm                        = p_yyyymm;
        
      COMMIT;                      
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        INSERT INTO                          TB_CM_INCENTIVE_BALANCE( 
                                             yyyymm                  ,region_id
                                            ,scheme_id               ,shop_code
                                            ,model                   ,qty
                                            ,point                   ,amt
                                            ,billto_code             ,billto_name)  
        VALUES                                                       (
                                             p_yyyymm                ,p_region
                                            ,p_scheme                ,p_shop_code
                                            ,p_model                 ,1
                                            ,p_point                 ,p_amt
                                            ,v_billto_code           ,v_billto_name);
          
        COMMIT;
      WHEN DUP_VAL_ON_INDEX THEN NULL;         
    END;  
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      BEGIN
        SELECT                               'Y'
        INTO                                 v_inct_bal_ck
        FROM                                 TB_CM_INCENTIVE_BALANCE
        WHERE  region_id                   = p_region
        AND    scheme_id                   = p_scheme
        AND    shop_code                   = p_shop_code
        AND    model                       = p_model
        AND    yyyymm                      = p_yyyymm;
          
        UPDATE                               TB_CM_INCENTIVE_BALANCE
        SET     point                      = TO_NUMBER(point) + p_point
               ,amt                        = TO_NUMBER(amt)   + p_amt
               ,qty                        = TO_NUMBER(qty)   + 1
               ,update_date                = sysdate
        WHERE  region_id                   = p_region
        AND    scheme_id                   = p_scheme
        AND    shop_code                   = p_shop_code
        AND    model                           = p_model
        AND    yyyymm                      = p_yyyymm;
          
        COMMIT;                      
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          INSERT INTO                        TB_CM_INCENTIVE_BALANCE( 
                                             yyyymm                  ,region_id
                                            ,scheme_id               ,shop_code
                                            ,model                   ,qty
                                            ,point                   ,amt
                                            ,billto_code             ,billto_name)  
          VALUES                                              (
                                             p_yyyymm                ,p_region
                                            ,p_scheme                ,p_shop_code
                                            ,p_model                 ,1
                                            ,p_point                 ,p_amt
                                            ,v_billto_code           ,v_billto_name);
            
          COMMIT;
        WHEN DUP_VAL_ON_INDEX THEN NULL;         
      END;
    WHEN OTHERS THEN NULL;
  END PR_SO_INCT_BAL_SV;    
  -----------------------------------------------------------------------------------------------------
  --                           PROCEDURE INCENTIVE BALANCE INQUIRY BY SHOP
  -----------------------------------------------------------------------------------------------------
  PROCEDURE PR_SO_INCT_BAL_BILL_INQ(
            out_cur            OUT          SYS_REFCURSOR
           ,p_ms_yyyymm        IN           VARCHAR2 DEFAULT TO_CHAR(sysdate,'YYYYMM')
           ,p_me_yyyymm        IN           VARCHAR2 DEFAULT TO_CHAR(sysdate,'YYYYMM')
           ,p_billto_name      IN           VARCHAR2 DEFAULT null
           ,p_user_region      IN           VARCHAR2 DEFAULT '00'
           ,p_region_id        IN           VARCHAR2 DEFAULT '%')
  -----------------------------------------------------------------------------------------------------
  IS
           v_region_id                      VARCHAR2(2) := null;
  -----------------------------------------------------------------------------------------------------
  BEGIN
    IF     p_user_region                  = '00' THEN
           v_region_id                   := p_region_id;
    ELSE
           v_region_id                   := p_user_region;
    END IF;
    OPEN   out_cur FOR
      SELECT r.name                             REGION
            ,UPPER(p.name)                      PROVINCE
            ,UPPER(s.shop_name)                 CUST_NAME
            ,UPPER(s.address)                   ADDRESS
            ,si.billto_code                     BILLTO
            ,si.billto_name                     BILL_NAME
            ,m.prod_l1                          PRODUCT
            ,so.model                           MODEL
            ,TO_CHAR(TO_NUMBER(COUNT(so.serial_no)),'999G999','NLS_NUMERIC_CHARACTERS = '',.')     
                                                QTY
            ,TO_CHAR(TO_NUMBER(SUM(so.point),'9999999'),'999G999','NLS_NUMERIC_CHARACTERS = '',.') 
                                                WT_AMT
            ,TO_CHAR(TO_NUMBER(SUM(so.amt),'999999999'),'999G999G999','NLS_NUMERIC_CHARACTERS = '',.') 
                                                INCT_AMT
            ,i.scheme_name                      SCHEME 
      FROM   TB_SN_SO_WT_MST                    SO
            ,(
             SELECT o1.billto_code              BILLTO_CODE
                   ,o1.billto_name              BILLTO_NAME
                   ,o1.model                    MODEL 
                   ,c.serial_no                 SERIAL_NO 
             FROM   TB_SN_CDC_HIST              C
                   ,TB_ORDER_SHIP_HIST          O1
             WHERE  c.inv_org(+)              = o1.inv_org
             AND    c.order_no(+)             = o1.order_no
             AND    c.model(+)                = o1.model
             AND    SUBSTR(c.serial_no,1,3)  >= '209'
             AND    sellout_flag              = 'Y'
           UNION
             SELECT o.billto_code               BILLTO_CODE
                   ,o.billto_name               BILLTO_NAME
                   ,o.model                     MODEL 
                   ,r1.serial_no                SERIAL_NO
             FROM   TB_SN_RDC_HIST              R1
                   ,TB_ORDER_SHIP_HIST          O
             WHERE  r1.inv_org(+)             = o.inv_org
             AND   (r1.ship_no                = o.order_no OR r1.edi_no              = o.pick_no) 
            -- AND    r1.edi_no(+)              = o.pick_no
             AND    r1.model(+)               = o.model
             AND    SUBSTR(r1.serial_no,1,3) >= '209'
             AND    sellout_status            = 'Y' ) 
                                                SI
            ,TB_CM_MODEL_CAT                    M
            ,TB_CM_SHOP_INF                     S
            ,TB_CM_REGION                       R
            ,TB_CM_INCENTIVE_MST                I
            ,TB_CM_PROVINCE                     P
      WHERE  so.model                         = m.model
      AND    so.shop_code                     = s.shop_code
      AND    so.incentive_cfm_period          = i.scheme_id
      AND    so.serial_no                     = si.serial_no
      AND    so.model                         = si.model
      AND    s.reg_id                         = r.id
      AND    s.province_code                  = p.id
      AND    so.incentive_cfm_period         != 'NA'
      AND    so.close_flag                    = 'N'
      AND    TO_CHAR(so.sellout_date,'YYYYMM')  BETWEEN p_ms_yyyymm AND p_me_yyyymm
      AND    r.id                          LIKE v_region_id
      AND    'ABN'||UPPER(si.billto_name)  LIKE DECODE(p_billto_name, null, '%', '%'||UPPER(p_billto_name)||'%')     
      GROUP BY
            r.name
           ,p.name                              
           ,si.billto_code                     
           ,si.billto_name                     
           ,s.shop_name                        
           ,s.address
           ,m.prod_l1
           ,so.model
           ,i.scheme_name
      ORDER BY
            r.name
           ,p.name                             
           ,s.shop_name  
           ,si.billto_code                     
           ,si.billto_name                     
           ,m.prod_l1                          
           ,so.model; 
    /*OPEN   out_cur FOR 
    SELECT r.name                           REGION
          ,DECODE(i.billto_code, null, 'OTHERs', i.billto_code)                    
                                            BILLTO_CODE
          ,DECODE(i.billto_name, null, 'OTHERs', i.billto_name)
                                            BILLTO_NAME
          ,s.shop_name                      SHOP_NAME
          ,m.prod_l1                        PRODUCT
          ,i.model                          MODEL
          ,TO_CHAR(SUM(i.qty),'999,999,999') 
                                            QTY
          ,TO_CHAR(SUM(i.point),'999,999,999') 
                                            POINT
          ,TO_CHAR(SUM(i.amt),'999,999,999')
                                            AMT
          ,t.scheme_name                    SCHEME                                  
    FROM   TB_CM_INCENTIVE_BALANCE          I
          ,TB_CM_REGION                     R
          ,TB_CM_SHOP_INF                   S
          ,TB_CM_MODEL_CAT                  M
          ,TB_CM_INCENTIVE_MST              T
    WHERE  s.reg_id                       = r.id
    AND    s.shop_code                 = i.shop_code
    AND    i.model                        = m.model
    AND    i.scheme_id                    = t.scheme_id
    AND    i.yyyymm              BETWEEN    p_ms_yyyymm AND p_me_yyyymm
    AND    r.id                        LIKE v_region_id
    AND    'ABN'||UPPER(i.billto_name) LIKE DECODE(p_billto_name, null, '%', '%'||UPPER(p_billto_name)||'%')
    GROUP BY
           r.name
          ,i.billto_code
          ,i.billto_name
          ,s.shop_name
          ,m.prod_l1
          ,i.model
          ,t.scheme_name 
    ORDER BY
           r.name
          ,i.billto_code
          ,s.shop_name
          ,m.prod_l1
          ,i.model;*/
  END PR_SO_INCT_BAL_BILL_INQ;
  -----------------------------------------------------------------------------------------------------
  --                           PROCEDURE INCENTIVE BALANCE INQUIRY BY SHOP
  -----------------------------------------------------------------------------------------------------
  PROCEDURE PR_SO_INCT_BAL_SHOP_INQ(
            out_cur            OUT          SYS_REFCURSOR
           ,p_ms_yyyymm        IN           VARCHAR2 DEFAULT TO_CHAR(sysdate,'YYYYMM')
           ,p_me_yyyymm        IN           VARCHAR2 DEFAULT TO_CHAR(sysdate,'YYYYMM')
           ,p_shop_name        IN           VARCHAR2 DEFAULT null
           ,p_user_region      IN           VARCHAR2 DEFAULT '00'
           ,p_region_id        IN           VARCHAR2 DEFAULT '%')
  -----------------------------------------------------------------------------------------------------
  IS
           v_region_id                      VARCHAR2(2) := null;
  -----------------------------------------------------------------------------------------------------
  BEGIN
    IF     p_user_region                  = '00' THEN
           v_region_id                   := p_region_id;
    ELSE
           v_region_id                   := p_user_region;
    END IF;
    
    OPEN   out_cur FOR 
    SELECT r.name                           REGION
          ,p.name                           PROVINCE 
          ,b.billto_code                    BELONG_TO_BILL
          ,UPPER(b.short_name)              BELONG_TO_CUSTOMER
          ,UPPER(s.shop_name)               CUST_NAME
          ,UPPER(s.address)                 ADDRESS
          ,m.prod_l1                        PRODUCT
          ,i.model                          MODEL
          ,TO_CHAR(TO_NUMBER(SUM(i.qty),'9999999'),'999G999','NLS_NUMERIC_CHARACTERS = '',.')  
                                            QTY
          ,TO_CHAR(TO_NUMBER(SUM(i.point),'9999999'),'999G999','NLS_NUMERIC_CHARACTERS = '',.') 
                                            WT_AMT 
          ,TO_CHAR(TO_NUMBER(SUM(i.amt),'999999999'),'999G999G999','NLS_NUMERIC_CHARACTERS = '',.')
                                            INCT_AMT
          ,t.scheme_name                    SCHEME
    FROM   TB_CM_INCENTIVE_BALANCE          I
          ,TB_CM_REGION                     R
          ,TB_CM_SHOP_INF                   S
          ,TB_CM_MODEL_CAT                  M
          ,TB_CM_PROVINCE                   P
          ,TB_CM_BILLTO_INF                 B
          ,TB_CM_INCENTIVE_MST              T
    WHERE  s.reg_id                       = r.id
    AND    s.province_code                = p.id
    AND    s.billto_code                  = b.billto_code
    AND    s.shop_code                    = i.shop_code
    AND    i.model                        = m.model
    AND    i.scheme_id                    = t.scheme_id
    AND    i.yyyymm                BETWEEN  p_ms_yyyymm AND p_me_yyyymm
    AND    r.id                        LIKE v_region_id
    AND    UPPER(s.shop_name)          LIKE DECODE(p_shop_name, null, '%', '%'||UPPER(p_shop_name)||'%')
    GROUP BY
           r.name
          ,p.name
          ,s.shop_name
          ,s.address
          ,b.billto_code
          ,b.short_name
          ,m.prod_l1
          ,i.model
          ,t.scheme_name 
    ORDER BY
           r.name
          ,p.name
          ,b.billto_code 
          ,s.shop_name
          ,m.prod_l1
          ,i.model;
  END PR_SO_INCT_BAL_SHOP_INQ;
  
  -----------------------------------------------------------------------------------------------------
  --                           FUNCTION SERIAL UPLOADING VALIDATION
  -----------------------------------------------------------------------------------------------------
  FUNCTION FN_SO_UPL_VAL(        
           p_shop_code             VARCHAR2
          ,p_model                 VARCHAR2 DEFAULT null
          ,p_serial_no             VARCHAR2
          ,p_end_user_cell         VARCHAR2)
  RETURN                           VARCHAR2
  -----------------------------------------------------------------------------------------------------
  IS     
    v_shop_code                    VARCHAR2(10)  := null;
    v_product                      VARCHAR2(10)  := null;
    v_model                        VARCHAR2(20)  := null;
    v_serial_no                    VARCHAR2(50)  := UPPER(p_serial_no);
    v_end_user_cell                VARCHAR2(20)  := p_end_user_cell;
    v_sub_sn                       NUMBER  (03)  := TO_NUMBER(SUBSTR(v_serial_no,1,3));
    v_shop_dup                     VARCHAR2(150) := null;
    v_sn_vrf                       SN_VRF_TYPE   := SN_VRF_TYPE(null, null, null, null);
      
    v_return                       VARCHAR2(500) := null;
  -----------------------------------------------------------------------------------------------------
  BEGIN
    --Validating the sell-out shop_code
    IF p_shop_code              IS NULL THEN
      v_return                  := 'Shop code ko duoc empty.';
          
      RETURN                       v_return;
    END IF;  
    BEGIN
      SELECT                       shop_code
      INTO                         v_shop_code
      FROM                         TB_CM_SHOP_INF
      WHERE  shop_code           = p_shop_code
      AND    use_flag            = 'Y';
    EXCEPTION
      WHEN OTHERS THEN
        v_return                := 'Shop ' || p_shop_code 
                                   || ' chua duoc dang ky.';
          
        RETURN                     v_return;
    END;  
    --Validating the sell-out model
    IF p_model                  IS NOT NULL THEN
      BEGIN
        SELECT                     prod_l1 
                                  ,model
        INTO                       v_product
                                  ,v_model
        FROM                       TB_CM_MODEL_CAT
        WHERE  model             = UPPER(p_model);
        IF (v_product            = 'AC') 
        AND 
           (SUBSTR(v_model,-1,1)!= 'N' 
        OR 
           SUBSTR(v_model,3,1)  != 'N')  THEN 
         v_return               := v_return ||'Kiem tra lai ' || v_model 
                                   || ' va kich hoat dich vu voi ten Model v? so Serial duoc in tren dan lanh (Indoor).';
           
         RETURN                    v_return;
        END IF;
      EXCEPTION
        WHEN OTHERS THEN
          v_return              := v_return || 'Model ' || p_model 
                                   || ' khong dung.';
            
          RETURN                   v_return;
      END;
    ELSE
      -- Serial is AC but not including Model
      IF (SUBSTR(v_serial_no,5,1)= 'A') 
        AND 
         (SUBSTR(v_serial_no,4,1)!='T')THEN 
            v_return             := v_return ||'Serial '|| v_serial_no 
                                             || ' dieu hoa, can co thong tin model de duoc xac nhan/';
           
         RETURN                     v_return;
      END IF;
    END IF;
    -- Validating end_user's cell#
    IF v_end_user_cell          IS NOT NULL THEN
      IF LENGTH(v_end_user_cell) NOT IN (9,10,11) THEN
        v_return                := v_return || 'So DT KH ko dung/';
      END IF;  
    END IF;
    --Validating serial & model
    IF pk_sms_mst.FN_SN_VALIDATE(v_serial_no) = FALSE THEN
        v_return                := v_return || 'Serial ' || v_serial_no ||' khong dung.';
          
        RETURN                     v_return;
    END IF;
    IF v_sub_sn                  < pk_sms_mst.const_wt_outdate THEN
      v_return                  := v_return ||'San pham khong thuoc chuong trinh Bao hanh thong minh.';
          
      RETURN                       v_return;
    END IF;
    --Gathering the required data data 
    BEGIN 
      -- Check serial_no duplicate registration.
      SELECT                       b.shop_code ||':'||b.shop_name
      INTO                         v_shop_dup
      FROM                         TB_SN_SO_WT_MST  A
                                  ,TB_CM_SHOP_INF   B 
      WHERE  a.shop_code         = b.shop_code  
      AND    a.serial_no         = v_serial_no
      AND    a.model          LIKE DECODE(p_model, null, '%', p_model);
            
      v_return                  := v_return || 'Serial '|| v_serial_no 
                                            || ' da duoc dang ky boi shop ' || v_shop_dup;
          
      RETURN                       v_return;
    -----------------------------------------------------------------------------------------------------
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        v_sn_vrf                := pk_sms_mst.FN_SN_VERIFY( v_serial_no ,v_model);
            
        IF v_sn_vrf.vrf_flag     = 'M' THEN
          v_return              := v_return || 'Serial '|| v_serial_no 
                                            || ' dieu hoa, can co thong tin model de duoc sac nhan.';
              
          RETURN                   v_return;
        ELSIF v_sn_vrf.vrf_flag  = 'D' THEN
         --- Check duplication in pending table
          BEGIN
            SELECT                 serial_no  
            INTO                   v_shop_dup
            FROM                   TB_SN_PND_HIST
            WHERE  serial_no     = p_serial_no
            AND    'ABN'||model   LIKE DECODE(p_model, null, '%', '%'|| p_model)
            AND    cmdcode       = pk_sms_mst.const_cmdcode_so;
            v_return            := v_return || 'Serial '|| v_serial_no || 'dang tro xac nhan.';
                
            RETURN                 v_return;
          EXCEPTION
            WHEN OTHERS THEN 
              v_return          := v_return || 'PND';     
          END;
        ELSE
          v_return              := v_return || 'SO';     
        END IF;                                        
    END;
      
    RETURN                         v_return;
  EXCEPTION
    WHEN OTHERS THEN RETURN SQLERRM;     
  END FN_SO_UPL_VAL;
  -----------------------------------------------------------------------------------------------------
  --                           PROCEDURE SAVING SERIAL UPLOAD
  -----------------------------------------------------------------------------------------------------
  FUNCTION FN_SO_UPL_SV(       
           p_vrf_flag              VARCHAR2
          ,p_shop_code             VARCHAR2
          ,p_model                 VARCHAR2 DEFAULT null
          ,p_serial_no             VARCHAR2
          ,p_end_user_cell         VARCHAR2 DEFAULT null)
  RETURN                           VARCHAR2
  -----------------------------------------------------------------------------------------------------
  IS                               PRAGMA AUTONOMOUS_TRANSACTION;
    v_shop_code                    VARCHAR2(10)  := p_shop_code;
    v_shop_cell                    VARCHAR2(20)  := null;
    v_end_user_cell                VARCHAR2(20)  := p_end_user_cell;
    v_model                        VARCHAR2(20)  := p_model;
    v_serial_no                    VARCHAR2(30)  := p_serial_no;
    v_suffix                       VARCHAR2(20)  := null;
    v_sellin_date                  DATE;
    v_sellout_date                 DATE          := sysdate;
    v_sellout_time                 VARCHAR2(08)  := null;
    v_resp_type                    VARCHAR2(10)  := null;
    v_resp_msg                     VARCHAR2(160) := null;
    v_sms_yn                       VARCHAR2(01)  :='N';
      
    v_sn_vrf                       SN_VRF_TYPE  := SN_VRF_TYPE(null, null, null, null);
    v_wt_resp                      WT_RESP_TYPE := WT_RESP_TYPE(null, null, null);
  -----------------------------------------------------------------------------------------------------
  BEGIN
    --CASE : product is verified 
    IF p_vrf_flag                = 'SO' THEN
      v_sn_vrf                  := pk_sms_mst.FN_SN_VERIFY( v_serial_no ,v_model );
                                            
      v_model                   := v_sn_vrf.model;
      v_suffix                  := v_sn_vrf.suffix;
      v_sellin_date             := v_sn_vrf.sellin_date;
      v_sellout_time            := TO_CHAR(v_sellout_date, 'HH24MISS');
      v_resp_type               := pk_sms_mst.const_so_sn_vl;
      v_resp_msg                := pk_sms_mst.FN_GET_MT_RESPONSE( v_resp_type ,'$' || v_model || ', Serial ' || v_serial_no); 
        
      --dbms_output.put_line ( v_resp_type || v_serial_no || '/' || v_model);
      --Update SO
      pk_sms_mst.PR_UPDATE_SN_SO_WT_MST(
                                        v_shop_code      ,v_shop_cell
                                       ,v_serial_no      ,v_model
                                       ,v_suffix         ,v_sellin_date
                                       ,v_sellout_date   ,v_sellout_time
                                       ,v_resp_type      ,v_resp_msg
                                       ,v_sms_yn         ,v_end_user_cell);
     --Update history
      pk_sms_mst.PR_UPDATE_SN_SO_WT_HIST(   
                                         v_serial_no     ,v_resp_type
                                        ,v_resp_msg      ,v_resp_msg
                                        ,v_sellout_date  ,v_shop_cell
                                        ,p_end_user_cell ,'N'
                                        ,'0'             ,'SO_UPLOAD'
                                        ,v_model         ,v_shop_code);                                                    
      
     --Change the sell-out status of the store
     UPDATE tb_sn_rdc_hist
     SET    sellout_status       = 'Y'
           ,sellout_date         = v_sellout_date
     WHERE  serial_no            = v_serial_no
     AND    model                = v_model
     AND    suffix               = v_suffix;
            
     UPDATE tb_sn_cdc_hist
     SET    sellout_flag         = 'Y'
           ,sellout_date         = v_sellout_date
     WHERE  serial_no            = v_serial_no
     AND    model                = v_model
     AND    suffix               = v_suffix;
            
     COMMIT; 
            
     IF v_end_user_cell         IS NOT NULL THEN
       v_wt_resp                := pk_sms_mst.FN_GET_WT_RESPONSE( v_serial_no ,v_model);
       IF v_wt_resp.wt_resp_flag = 'O' THEN
         IF v_wt_resp.wt_resp_year = 1 THEN
           v_resp_type              := const_wt_12_vl;
         ELSIF v_wt_resp.wt_resp_year = 2 THEN
           v_resp_type              := const_wt_24_vl;    
         END IF;
       END IF;  
       
       v_resp_msg               := pk_sms_mst.FN_GET_MT_RESPONSE( v_resp_type , v_wt_resp.wt_resp_msg);
               
        --Update history                   
       pk_sms_mst.PR_UPDATE_SN_SO_WT_HIST(   
                                          v_serial_no    ,v_resp_type
                                         ,v_resp_msg     ,v_resp_msg
                                        ,v_sellout_date  ,v_shop_cell
                                        ,p_end_user_cell ,'N'
                                        ,'0'             ,'WT_UPLOAD'
                                        ,v_model         ,v_shop_code);     
       --Update MT response for WT case
       INSERT INTO                 TB_MT_HIST(
                                   moseq                        ,cell_no
                                  ,cmdcode                      ,resp_type
                                  ,msgbody                      ,mo_msgbody
                                  ,mo_receive_time              ,mt_seq)
        VALUES                     (
                                   '0'                         ,v_end_user_cell
                                  ,pk_sms_mst.const_cmdcode_wt ,v_resp_type
                                  ,v_resp_msg                  ,'SO_UPLOAD'
                                  ,v_sellout_date              ,mt_seq.nextval);
        COMMIT;                                          
            
     END IF;
    --CASE : product is validated but not found in store => Add to pending table for verifying later.
   ELSE
     v_resp_type                := pk_sms_mst.const_sn_pnd_vl;
     v_resp_msg                 := pk_sms_mst.FN_GET_MT_RESPONSE( v_resp_type ,'$'|| v_serial_no);
                                                   
     pk_sms_mst.PR_UPDATE_PND_HIST(  
                                   v_serial_no     ,v_model
                                  ,'SO_UPLOAD'     ,'0'
                                  ,v_shop_code     ,v_shop_cell
                                  ,v_end_user_cell ,v_resp_type
                                  ,v_resp_type     ,v_resp_msg
                                  ,v_resp_msg      ,v_sellout_date
                                  ,'N');     
        
    END IF;
      
    RETURN 'DONE'; 
  -----------------------------------------------------------------------------------------------------    
  EXCEPTION
    WHEN OTHERS THEN RETURN SQLERRM;
        
  END FN_SO_UPL_SV;
  -----------------------------------------------------------------------------------------------------
  --                           PROCEDURE SHOP SELL-OUT MONTHLY INQUIRY BY YEAR
  -----------------------------------------------------------------------------------------------------
  PROCEDURE PR_SO_MTH_INQ( 
            out_cur               OUT           SYS_REFCURSOR
           ,p_yyyy                IN            VARCHAR2 DEFAULT TO_CHAR(sysdate,'YYYY')
           ,p_shop_code           IN            VARCHAR2 DEFAULT '%'
           ,p_product             IN            VARCHAR2 DEFAULT '%'
           ,p_model               IN            VARCHAR2 DEFAULT '%'
           ,p_user_region         IN            VARCHAR2 DEFAULT '00'
           ,p_region_id           IN            VARCHAR2 DEFAULT '%')
  -----------------------------------------------------------------------------------------------------
  IS
            v_jan_s                             VARCHAR2(10) := p_yyyy|| '0101';
            v_jan_e                             VARCHAR2(10) := TO_CHAR(LAST_DAY(TO_DATE(v_jan_s,'YYYYMMDD')),'YYYYMMDD');
       
            v_feb_s                             VARCHAR2(10) := p_yyyy|| '0201';
            v_feb_e                             VARCHAR2(10) := TO_CHAR(LAST_DAY(TO_DATE(v_feb_s,'YYYYMMDD')),'YYYYMMDD');
       
            v_mar_s                             VARCHAR2(10) := p_yyyy|| '0301';
            v_mar_e                             VARCHAR2(10) := TO_CHAR(LAST_DAY(TO_DATE(v_mar_s,'YYYYMMDD')),'YYYYMMDD');
       
            v_apr_s                             VARCHAR2(10) := p_yyyy|| '0401';
            v_apr_e                             VARCHAR2(10) := TO_CHAR(LAST_DAY(TO_DATE(v_apr_s,'YYYYMMDD')),'YYYYMMDD'); 
      
            v_may_s                             VARCHAR2(10) := p_yyyy|| '0501';
            v_may_e                             VARCHAR2(10) := TO_CHAR(LAST_DAY(TO_DATE(v_may_s,'YYYYMMDD')),'YYYYMMDD');
      
            v_jun_s                             VARCHAR2(10) := p_yyyy|| '0601';
            v_jun_e                             VARCHAR2(10) := TO_CHAR(LAST_DAY(TO_DATE(v_jun_s,'YYYYMMDD')),'YYYYMMDD');
      
            v_jul_s                             VARCHAR2(10) := p_yyyy|| '0701';
            v_jul_e                             VARCHAR2(10) := TO_CHAR(LAST_DAY(TO_DATE(v_jul_s,'YYYYMMDD')),'YYYYMMDD');
      
            v_aug_s                             VARCHAR2(10) := p_yyyy|| '0801';
            v_aug_e                             VARCHAR2(10) := TO_CHAR(LAST_DAY(TO_DATE(v_aug_s,'YYYYMMDD')),'YYYYMMDD');
      
            v_sep_s                             VARCHAR2(10) := p_yyyy|| '0901';
            v_sep_e                             VARCHAR2(10) := TO_CHAR(LAST_DAY(TO_DATE(v_sep_s,'YYYYMMDD')),'YYYYMMDD');
      
            v_oct_s                             VARCHAR2(10) := p_yyyy|| '1001';
            v_oct_e                             VARCHAR2(10) := TO_CHAR(LAST_DAY(TO_DATE(v_oct_s,'YYYYMMDD')),'YYYYMMDD');
      
            v_nov_s                             VARCHAR2(10) := p_yyyy|| '1101';
            v_nov_e                             VARCHAR2(10) := TO_CHAR(LAST_DAY(TO_DATE(v_nov_s,'YYYYMMDD')),'YYYYMMDD');
      
            v_dec_s                             VARCHAR2(10) := p_yyyy|| '1201';
            v_dec_e                             VARCHAR2(10) := TO_CHAR(LAST_DAY(TO_DATE(v_dec_s,'YYYYMMDD')),'YYYYMMDD');
      
            v_region_id                         VARCHAR2(2)  := null;        
  -----------------------------------------------------------------------------------------------------
  BEGIN
    IF      p_user_region                       = '00' THEN
            v_region_id                        := p_region_id;
    ELSE
            v_region_id                        := p_user_region;
    END IF;
      
    OPEN out_cur FOR
      SELECT CASE WHEN p_user_region           = '00' THEN region END
                                                 REGION
            ,UPPER(province)                     PROVINCE
            ,shop_code                           SHOP_CODE
            ,UPPER(shop_name)                    SHOP_NAME
            ,product                             PRODUCT
            ,model                               MODEL
            ,TO_CHAR(SUM(ACM),'999G999','NLS_NUMERIC_CHARACTERS = '',.')                            
                                                 ACM
            ,SUM(JAN)                            JAN
            ,SUM(FEB)                            FEB
            ,SUM(MAR)                            MAR
            ,SUM(APR)                            APR
            ,SUM(MAY)                            MAY
            ,SUM(JUN)                            JUN
            ,SUM(JUL)                            JUL
            ,SUM(AUG)                            AUG
            ,SUM(SEP)                            SEP
            ,SUM(OCT)                            OCT
            ,SUM(NOV)                            NOV
            ,SUM(DEC)                            DEC 
      FROM  (        
             SELECT  ae.region_name              REGION
                    ,ae.province_name            PROVINCE
                    ,ae.shop_code                SHOP_CODE
                    ,ae.shop_name                SHOP_NAME
                    ,so.product                  PRODUCT
                    ,so.model                    MODEL
                    ,CASE WHEN (TO_CHAR(so.sellout_date,'YYYYMMDD')>=v_jan_s) AND (TO_CHAR(so.sellout_date,'YYYYMMDD')<=v_jan_e) THEN count(so.serial_no) ELSE 0 END JAN
                    ,CASE WHEN (TO_CHAR(so.sellout_date,'YYYYMMDD')>=v_feb_s) AND (TO_CHAR(so.sellout_date,'YYYYMMDD')<=v_feb_e) THEN count(so.serial_no) ELSE 0 END FEB
                    ,CASE WHEN (TO_CHAR(so.sellout_date,'YYYYMMDD')>=v_mar_s) AND (TO_CHAR(so.sellout_date,'YYYYMMDD')<=v_mar_e) THEN count(so.serial_no) ELSE 0 END MAR
                    ,CASE WHEN (TO_CHAR(so.sellout_date,'YYYYMMDD')>=v_apr_s) AND (TO_CHAR(so.sellout_date,'YYYYMMDD')<=v_apr_e) THEN count(so.serial_no) ELSE 0 END APR
                    ,CASE WHEN (TO_CHAR(so.sellout_date,'YYYYMMDD')>=v_may_s) AND (TO_CHAR(so.sellout_date,'YYYYMMDD')<=v_may_e) THEN count(so.serial_no) ELSE 0 END MAY
                    ,CASE WHEN (TO_CHAR(so.sellout_date,'YYYYMMDD')>=v_jun_s) AND (TO_CHAR(so.sellout_date,'YYYYMMDD')<=v_jun_e) THEN count(so.serial_no) ELSE 0 END JUN
                    ,CASE WHEN (TO_CHAR(so.sellout_date,'YYYYMMDD')>=v_jul_s) AND (TO_CHAR(so.sellout_date,'YYYYMMDD')<=v_jul_e) THEN count(so.serial_no) ELSE 0 END JUL
                    ,CASE WHEN (TO_CHAR(so.sellout_date,'YYYYMMDD')>=v_aug_s) AND (TO_CHAR(so.sellout_date,'YYYYMMDD')<=v_aug_e) THEN count(so.serial_no) ELSE 0 END AUG
                    ,CASE WHEN (TO_CHAR(so.sellout_date,'YYYYMMDD')>=v_sep_s) AND (TO_CHAR(so.sellout_date,'YYYYMMDD')<=v_sep_e) THEN count(so.serial_no) ELSE 0 END SEP
                    ,CASE WHEN (TO_CHAR(so.sellout_date,'YYYYMMDD')>=v_oct_s) AND (TO_CHAR(so.sellout_date,'YYYYMMDD')<=v_oct_e) THEN count(so.serial_no) ELSE 0 END OCT
                    ,CASE WHEN (TO_CHAR(so.sellout_date,'YYYYMMDD')>=v_nov_s) AND (TO_CHAR(so.sellout_date,'YYYYMMDD')<=v_nov_e) THEN count(so.serial_no) ELSE 0 END NOV
                    ,CASE WHEN (TO_CHAR(so.sellout_date,'YYYYMMDD')>=v_dec_s) AND (TO_CHAR(so.sellout_date,'YYYYMMDD')<=v_dec_e) THEN count(so.serial_no) ELSE 0 END DEC
                    ,CASE WHEN (TO_CHAR(so.sellout_date,'YYYYMMDD')>=v_jan_s) AND (TO_CHAR(so.sellout_date,'YYYYMMDD')<=v_dec_e) THEN count(so.serial_no) ELSE 0 END ACM
              FROM   ( 
                      SELECT m.prod_l1           PRODUCT
                            ,sw.shop_code        SHOP_CODE
                            ,sw.model            MODEL
                            ,sw.serial_no        SERIAL_NO
                            ,sw.sellout_date     SELLOUT_DATE
                      FROM   tb_sn_so_wt_mst     SW
                            ,tb_cm_model_cat     M
                      WHERE  sw.model          = m.model) 
                                                 SO      
                    ,(      
                      SELECT s.reg_id            REGION_ID
                            ,r.name              REGION_NAME
                            ,p.name              PROVINCE_NAME
                            ,s.shop_code         SHOP_CODE
                            ,s.shop_name         SHOP_NAME
                      FROM   tb_cm_shop_inf      S
                            ,tb_cm_province      P
                            ,tb_cm_region        R
                      WHERE  s.province_code   = p.id
                      AND    p.region_id       = r.id)
                                                 AE
              WHERE   so.shop_code             = ae.shop_code
              AND     TO_CHAR(so.sellout_date,'YYYYMMDD') BETWEEN v_jan_s AND v_dec_e 
              AND     ae.shop_code          LIKE UPPER(p_shop_code)
              AND     so.product            LIKE UPPER(p_product)
              AND     so.model              LIKE UPPER(p_model)
              AND     ae.region_id          LIKE v_region_id
              GROUP BY
                       region_name
                      ,province_name
                      ,ae.shop_code
                      ,shop_name
                      ,product
                      ,model
                      ,sellout_date)             AA
       GROUP BY 
               region
              ,province
              ,shop_code
              ,shop_name
              ,product
              ,model
       ORDER BY
               region
              ,province
              ,shop_code
              ,product
              ,model;
  END  PR_SO_MTH_INQ;
  -----------------------------------------------------------------------------------------------------
  --                           PROCEDURE SELL-OUT WEEKLY INQUITY
  -----------------------------------------------------------------------------------------------------
  PROCEDURE PR_SO_WKL_INQ(         
            out_cur           OUT                SYS_REFCURSOR
           ,p_yyyymm          IN                 VARCHAR2       DEFAULT TO_CHAR(sysdate, 'YYYYMM')
           ,p_shop_code       IN                 VARCHAR2       DEFAULT '%'
           ,p_product         IN                 VARCHAR2       DEFAULT '%'
           ,p_model           IN                 VARCHAR2       DEFAULT '%'
           ,p_user_region     IN                 VARCHAR2       DEFAULT '00'
           ,p_region_id       IN                 VARCHAR2       DEFAULT '%')
  -----------------------------------------------------------------------------------------------------
  IS 
            v_ms_date                            DATE          := TO_DATE(p_yyyymm||'01','YYYYMMDD');
            
            v_w1s_date                           VARCHAR2(10)  := TO_CHAR(TRUNC(v_ms_date,'Day')+1,'YYYYMMDD');
            v_w1e_date                           VARCHAR2(10)  := TO_CHAR(TRUNC(TO_DATE(v_w1s_date,'YYYYMMDD'),'Day') +7,'YYYYMMDD');
            
            v_w2s_date                           VARCHAR2(10)  := TO_CHAR(TRUNC(TO_DATE(v_w1e_date,'YYYYMMDD'),'Day') +1,'YYYYMMDD');
            v_w2e_date                           VARCHAR2(10)  := TO_CHAR(TRUNC(TO_DATE(v_w2s_date,'YYYYMMDD'),'Day') +7,'YYYYMMDD');
            
            v_w3s_date                           VARCHAR2(10)  := TO_CHAR(TRUNC(TO_DATE(v_w2e_date,'YYYYMMDD'),'Day') +1,'YYYYMMDD');
            v_w3e_date                           VARCHAR2(10)  := TO_CHAR(TRUNC(TO_DATE(v_w3s_date,'YYYYMMDD'),'Day') +7,'YYYYMMDD');
            
            v_w4s_date                           VARCHAR2(10)  := TO_CHAR(TRUNC(TO_DATE(v_w3e_date,'YYYYMMDD'),'Day') +1,'YYYYMMDD');
            v_w4e_date                           VARCHAR2(10)  := TO_CHAR(TRUNC(TO_DATE(v_w4s_date,'YYYYMMDD'),'Day') +7,'YYYYMMDD');
            
            v_w5s_date                           VARCHAR2(10)  := TO_CHAR(TRUNC(TO_DATE(v_w4e_date,'YYYYMMDD'),'Day') +1,'YYYYMMDD');
            v_w5e_date                           VARCHAR2(10)  := TO_CHAR(TRUNC(TO_DATE(v_w5s_date,'YYYYMMDD'),'Day') +7,'YYYYMMDD');
            
            v_region_id                          VARCHAR2(02)  := null;
  -----------------------------------------------------------------------------------------------------
  BEGIN
    IF      p_user_region                      = '00' THEN
            v_region_id                       := p_region_id;
    ELSE
            v_region_id                       := p_user_region;
    END IF;
      
    OPEN out_cur FOR
      SELECT  CASE WHEN p_user_region          = '00' THEN region END
                                                 REGION
             ,UPPER(province)                    PROVINCE
             ,shop_code                          SHOP_CODE
             ,UPPER(shop_name)                   SHOP_NAME
             ,product                            PRODUCT
             ,model                              MODEL
             ,TO_CHAR(SUM(ACM),'999G999','NLS_NUMERIC_CHARACTERS = '',.')                           
                                                 ACM
             ,SUM(w1)                            W1
             ,SUM(w2)                            W2
             ,SUM(w3)                            W3
             ,SUM(w4)                            W4
             ,SUM(w5)                            W5
      FROM  (        
             SELECT  ae.region                   REGION
                    ,ae.province                 PROVINCE
                    ,ae.shop_code                SHOP_CODE
                    ,ae.shop_name                SHOP_NAME
                    ,so.product                  PRODUCT
                    ,so.model                    MODEL
                    ,CASE WHEN (TO_CHAR(so.sellout_date,'YYYYMMDD')>=v_w1s_date) AND (TO_CHAR(so.sellout_date,'YYYYMMDD')<=v_w1e_date) THEN 
                          COUNT(so.serial_no) ELSE 0 END 
                                                 W1
                    ,CASE WHEN (TO_CHAR(so.sellout_date,'YYYYMMDD')>=v_w2s_date) AND (TO_CHAR(so.sellout_date,'YYYYMMDD')<=v_w2e_date) THEN 
                          COUNT(so.serial_no) ELSE 0 END 
                                                 W2
                    ,CASE WHEN (TO_CHAR(so.sellout_date,'YYYYMMDD')>=v_w3s_date) AND (TO_CHAR(so.sellout_date,'YYYYMMDD')<=v_w3e_date) THEN 
                          COUNT(so.serial_no) ELSE 0 END 
                                                 W3
                    ,CASE WHEN (TO_CHAR(so.sellout_date,'YYYYMMDD')>=v_w4s_date) AND (TO_CHAR(so.sellout_date,'YYYYMMDD')<=v_w4e_date) THEN 
                          COUNT(so.serial_no) ELSE 0 END 
                                                 W4
                    ,CASE WHEN (TO_CHAR(so.sellout_date,'YYYYMMDD')>=v_w5s_date) AND (TO_CHAR(so.sellout_date,'YYYYMMDD')<=v_w5e_date) THEN 
                          COUNT(so.serial_no) ELSE 0 END 
                                                 W5                             
                    ,CASE WHEN (TO_CHAR(so.sellout_date,'YYYYMMDD')>=v_w1s_date) AND (TO_CHAR(so.sellout_date,'YYYYMMDD')<=v_w5e_date) THEN 
                          COUNT(so.serial_no) ELSE 0 END 
                                                 ACM                                                                                       
             FROM  ( 
                     SELECT m.prod_l1            PRODUCT
                           ,sw.shop_code         SHOP_CODE
                           ,sw.model             MODEL
                           ,sw.serial_no         SERIAL_NO
                           ,sw.sellout_date      SELLOUT_DATE
                     FROM   TB_SN_SO_WT_MST      SW
                           ,TB_CM_MODEL_CAT      M
                     WHERE  sw.model           = m.model)     
                                                 SO      
                  ,(      
                     SELECT s.reg_id             REGION_ID
                           ,r.name               REGION
                           ,p.name               PROVINCE
                           ,s.shop_code          SHOP_CODE
                           ,s.shop_name          SHOP_NAME
                     FROM   TB_CM_SHOP_INF       S
                           ,TB_CM_PROVINCE       P
                           ,TB_CM_REGION         R
                     WHERE  s.province_code    = p.id
                     AND    p.region_id        = r.id)
                                                 AE
             WHERE   so.shop_code              = ae.shop_code
             AND     TO_CHAR(so.sellout_date,'YYYYMMDD') BETWEEN v_w1s_date AND v_w5e_date
             AND     ae.shop_code           LIKE UPPER(p_shop_code)
             AND     so.product             LIKE UPPER(p_product)
             AND     so.model               LIKE UPPER(p_model)
             AND     ae.region_id           LIKE v_region_id
             GROUP BY
                     region
                    ,province
                    ,ae.shop_code
                    ,shop_name
                    ,product
                    ,model
                    ,sellout_date)               AA
      GROUP BY 
             region
            ,PROVINCE
            ,shop_code
            ,SHOP_NAME
            ,PRODUCT
            ,MODEL
      ORDER BY
             region
            ,province
            ,shop_code
            ,product
            ,model;
  END PR_SO_WKL_INQ;
  -----------------------------------------------------------------------------------------------------
  --                           PROCEDURE SELL-OUT RESULT BY WEEK
  -----------------------------------------------------------------------------------------------------
  PROCEDURE PR_SO_WKL_QTY_INQ(
            out_cur            OUT     SYS_REFCURSOR
           ,p_ws_date          IN      VARCHAR2       DEFAULT TO_CHAR(TRUNC(sysdate,'Day')+1 ,'YYYYMMDD')
           ,p_we_date          IN      VARCHAR2       DEFAULT TO_CHAR(TRUNC(sysdate,'Day')+7 ,'YYYYMMDD')
           ,p_shop_name        IN      VARCHAR2       DEFAULT null
           ,p_product          IN      VARCHAR2       DEFAULT '%'
           ,p_model            IN      VARCHAR2       DEFAULT null
           ,p_user_region      IN      VARCHAR2       DEFAULT '00'
           ,p_region           IN      VARCHAR2       DEFAULT '%')
  -----------------------------------------------------------------------------------------------------         
  IS
           v_region                    VARCHAR2(2)   := null;
  -----------------------------------------------------------------------------------------------------
  BEGIN
    IF     p_user_region             = '00' THEN
           v_region                 := p_region;
    ELSE
           v_region                 := p_user_region;
    END IF;    
    OPEN out_cur FOR
      SELECT CASE WHEN p_user_region = '00' THEN r.name END
                                       REGION
            ,UPPER(s.shop_name)        SHOP_NAME
            ,m.prod_l1                 PRODUCT
            ,m.model                   MODEL
            ,CASE WHEN so.incentive_cfm_period  = 'NA' THEN TO_CHAR(TO_NUMBER(COUNT(so.serial_no),'999999'),'999G999','NLS_NUMERIC_CHARACTERS = ''.,') ELSE TO_CHAR(0) END 
                                       NON_INCENTIVE_QTY
            ,CASE WHEN so.incentive_cfm_period != 'NA' THEN TO_CHAR(TO_NUMBER(COUNT(so.serial_no),'999999'),'999G999','NLS_NUMERIC_CHARACTERS = ''.,') ELSE TO_CHAR(0) END 
                                       INCENTIVE_QTY  
            --,COUNT(so.serial_no)       QTY
            ,TO_CHAR(TO_NUMBER(COUNT(so.serial_no),'999999'),'999G999','NLS_NUMERIC_CHARACTERS = ''.,')
                                       QTY                                                         
            --,SUM(so.point) + SUM(so.amt) 
            ,TO_CHAR(TO_NUMBER(SUM(so.point) + SUM(so.amt)),'999G999','NLS_NUMERIC_CHARACTERS = ''.,')     
                                       AMT 
      FROM   TB_SN_SO_WT_MST           SO
            ,TB_CM_REGION              R
            ,TB_CM_SHOP_INF            S
            ,TB_CM_MODEL_CAT           M
      WHERE  s.shop_code             = so.shop_code
      AND    s.reg_id                = r.id
      AND    so.model                = m.model
      AND    TO_CHAR(so.sellout_date,'YYYYMMDD') BETWEEN p_ws_date AND p_we_date
      AND    r.id                 LIKE v_region
      AND    UPPER(s.shop_name)   LIKE DECODE(p_shop_name, null, '%', '%'||UPPER(p_shop_name)||'%')
      AND    m.prod_l1            LIKE p_product
      AND    m.model              LIKE DECODE(p_model,     null, '%', '%'||UPPER(p_model)    ||'%')
      GROUP BY
             r.name
            ,s.shop_name
            ,m.prod_l1
            ,m.model
            ,so.incentive_cfm_period
      ORDER BY
             r.name
            ,s.shop_name
            ,m.prod_l1
            ,m.model;

  END PR_SO_WKL_QTY_INQ;  
  -----------------------------------------------------------------------------------------------------
  --                          PROCEDURE SELL-OUT DAILY INQUIRY
  -----------------------------------------------------------------------------------------------------
  PROCEDURE PR_SO_DLY_INQ(         
            out_cur         OUT                 SYS_REFCURSOR
           ,p_ws_date       IN                  VARCHAR2      DEFAULT TO_CHAR(TRUNC(sysdate,'Day') +1,'YYYYMMDD')
           ,p_we_date       IN                  VARCHAR2      DEFAULT TO_CHAR(TRUNC(sysdate,'Day') +7,'YYYYMMDD')
           ,p_shop_code     IN                  VARCHAR2      DEFAULT '%'
           ,p_product       IN                  VARCHAR2      DEFAULT '%'
           ,p_model         IN                  VARCHAR2      DEFAULT '%'
           ,p_user_region   IN                  VARCHAR2      DEFAULT '00'
           ,p_region_id     IN                  VARCHAR2      DEFAULT '%')
  -----------------------------------------------------------------------------------------------------
  IS 
           v_region_id                          VARCHAR2(2)   := null;
  ----------------------------------------------------------------------------------------------------- 
  BEGIN
    IF     p_user_region                      = '00' THEN
           v_region_id                       := p_region_id;
    ELSE
           v_region_id                       := p_user_region;
    END IF;
          
    OPEN out_cur FOR  
      SELECT  CASE WHEN p_user_region         = '00' THEN region END
                                                REGION
             ,UPPER(province)                   PROVINCE
             ,shop_code                         SHOP_CODE
             ,UPPER(shop_name)                  SHOP_NAME
             ,product                           PRODUCT
             ,model                             MODEL
             ,TO_CHAR(SUM(ACM),'999G999','NLS_NUMERIC_CHARACTERS = '',.')                          
                                                SUM
             ,SUM(mon)                          MON
             ,SUM(tue)                          TUE
             ,SUM(wed)                          WED
             ,SUM(thu)                          THU
             ,SUM(fri)                          FRI
             ,SUM(sat)                          SAT
             ,SUM(sun)                          SUN
      FROM  (        
              SELECT  ae.region                 REGION
                     ,ae.province               PROVINCE
                     ,ae.shop_code              SHOP_CODE
                     ,ae.shop_name              SHOP_NAME
                     ,so.product                PRODUCT
                     ,so.model                  MODEL
                     ,CASE WHEN (TO_CHAR(so.sellout_date,'YYYYMMDD')= p_ws_date)    THEN COUNT(so.serial_no) ELSE 0 END 
                                                MON
                     ,CASE WHEN (TO_CHAR(so.sellout_date,'YYYYMMDD') = TO_CHAR(TO_DATE(p_ws_date,'YYYYMMDD') +1,'YYYYMMDD')) THEN COUNT(so.serial_no) ELSE 0 END 
                                                TUE
                     ,CASE WHEN (TO_CHAR(so.sellout_date,'YYYYMMDD')= TO_CHAR(TO_DATE(p_ws_date,'YYYYMMDD') +2,'YYYYMMDD')) THEN COUNT(so.serial_no) ELSE 0 END 
                                                WED
                     ,CASE WHEN (TO_CHAR(so.sellout_date,'YYYYMMDD')= TO_CHAR(TO_DATE(p_ws_date,'YYYYMMDD') +3,'YYYYMMDD')) THEN COUNT(so.serial_no) ELSE 0 END 
                                                THU
                     ,CASE WHEN (TO_CHAR(so.sellout_date,'YYYYMMDD')= TO_CHAR(TO_DATE(p_ws_date,'YYYYMMDD') +4,'YYYYMMDD')) THEN COUNT(so.serial_no) ELSE 0 END 
                                                FRI
                     ,CASE WHEN (TO_CHAR(so.sellout_date,'YYYYMMDD')= TO_CHAR(TO_DATE(p_ws_date,'YYYYMMDD') +5,'YYYYMMDD')) THEN COUNT(so.serial_no) ELSE 0 END 
                                                SAT
                     ,CASE WHEN (TO_CHAR(so.sellout_date,'YYYYMMDD')= TO_CHAR(TO_DATE(p_ws_date,'YYYYMMDD') +6,'YYYYMMDD'))    THEN COUNT(so.serial_no) ELSE 0 END 
                                                SUN
                     ,CASE WHEN (TO_CHAR(so.sellout_date,'YYYYMMDD')>= p_ws_date)   
                           AND (TO_CHAR(so.sellout_date,'YYYYMMDD')<= p_we_date)    THEN COUNT(so.serial_no) ELSE 0 END 
                                                ACM   
              FROM   ( 
                      SELECT m.prod_l1          PRODUCT
                            ,sw.shop_code       SHOP_CODE
                            ,sw.model           MODEL
                            ,sw.serial_no       SERIAL_NO
                            ,sw.sellout_date    SELLOUT_DATE
                      FROM   TB_SN_SO_WT_MST    SW
                            ,TB_CM_MODEL_CAT    M
                      WHERE  sw.model         = m.model)   
                                                SO      
                    ,(      
                      SELECT s.reg_id           REGION_ID
                            ,r.name             REGION
                            ,p.name             PROVINCE
                            ,s.shop_code        SHOP_CODE
                            ,s.shop_name        SHOP_NAME
                      FROM   TB_CM_SHOP_INF     S
                            ,TB_CM_PROVINCE     P
                            ,tb_cm_region       R
                      WHERE  s.province_code  = p.id
                      AND    p.region_id      = r.id)
                                                AE
              WHERE   so.shop_code            = ae.shop_code
              AND     TO_CHAR(so.sellout_date,'YYYYMMDD') BETWEEN p_ws_date AND p_we_date
              AND     ae.shop_code         LIKE UPPER(p_shop_code)
              AND     so.product           LIKE UPPER(p_product)
              AND     so.model             LIKE UPPER(p_model)
              AND     ae.region_id         LIKE v_region_id
              GROUP BY
                      region
                     ,province
                     ,ae.shop_code
                     ,shop_name
                     ,product
                     ,model
                     ,sellout_date)             AA
       GROUP BY 
                region
               ,PROVINCE
               ,shop_code
               ,SHOP_NAME
               ,PRODUCT
               ,MODEL
       ORDER BY
                region
               ,province
               ,shop_code
               ,product
               ,model;
  END PR_SO_DLY_INQ;                                                      
  -----------------------------------------------------------------------------------------------------
  --                          PROCEDURE DAILY SO REGISTRATION INQUIRY
  -----------------------------------------------------------------------------------------------------
  PROCEDURE PR_SO_DLY_REG_INQ(     
            out_cur           OUT             SYS_REFCURSOR  
           ,p_ds_date         IN              VARCHAR2      DEFAULT TO_CHAR(sysdate, 'YYYYMMDD')
           ,p_de_date         IN              VARCHAR2      DEFAULT TO_CHAR(sysdate, 'YYYYMMDD')
           ,p_shop_code       IN              VARCHAR2      DEFAULT null
           ,p_shop_name       IN              VARCHAR2      DEFAULT null
           ,p_prod_l1         IN              VARCHAR2      DEFAULT null
           ,p_model           IN              VARCHAR2      DEFAULT null
           ,p_incentive_flag  IN              VARCHAR2      DEFAULT '%'
           ,p_sms_yn          IN              VARCHAR2      DEFAULT '%'
           ,p_user_region     IN              VARCHAR2      DEFAULT '00'
           ,p_region_id       IN              VARCHAR2      DEFAULT '%'
           ,p_serial_no       IN              VARCHAR2      DEFAULT null)
  -----------------------------------------------------------------------------------------------------
  IS
            v_region_id                       VARCHAR2(02)  :=  null;
  -----------------------------------------------------------------------------------------------------
  BEGIN
    IF      p_user_region                   = '00' THEN
            v_region_id                    := p_region_id;
    ELSE
            v_region_id                    := p_user_region;
    END IF;
          
    OPEN out_cur FOR  
      SELECT CASE WHEN p_user_region        = '00' THEN sr.region END 
                                              REGION
            ,UPPER(sr.shop_name)              CUST_NAME
            ,sr.cust_type                     CUST_TYPE
            ,UPPER(sr.province)               PROVINCE
            ,sr.b_bill                        BELONG_TO_BILL
            ,sr.channel                       CHANNEL
            ,so.shop_cell                     SHOP_CELL
            ,so.end_user_cell                 USER_CELL
            ,si.billto_code                   BILLCODE
            ,si.billto_name                   BILLNAME
            ,CASE WHEN m.prod_l1 IN ('WM','RF','CA','VC')  THEN 'HA'
                  WHEN m.prod_l1 IN ('TV','AV','HT','DS','CS','CD')  THEN 'HE'
                  WHEN m.prod_l1 IN ('AC')       THEN 'RAC'
                  WHEN m.prod_l1 IN ('MN','CD','PC')  THEN 'ITP'
                  ELSE 'OTHERS'  
             END                              DIVISION
            ,m.prod_l1                        PROD_L1
            ,m.prod_l2                        PROD_L2
            ,m.prod_l3                        PROD_L3
            ,m.prod_l4                        PROD_L4
            ,si.grade                         GRADE
            ,si.model                         MODEL
            ,si.model ||'.'||so.suffix        MODEL_SUFFIX
            ,TO_CHAR(TO_NUMBER(m.mrp,'999999999'),'99G999G999','NLS_NUMERIC_CHARACTERS = '',.')                            
                                              MRP            
            ,so.serial_no                     SERIAL_NO
            ,so.sellin_date                   SELLIN_DATE
            ,so.sellout_date                  SELLOUT_DATE
            ,TO_CHAR(so.amt + so.point,'999G999','NLS_NUMERIC_CHARACTERS = '',.')    
                                              AMOUNT  
            --,so.incentive_cfm_flag            CFM_FLAG
            ,so.incentive_cfm_date            CFM_DATE
            ,pk_sms_mst.FN_SMS_TRANS(so.sellout_resp_msg)
                                              SELLOUT_MSG
      FROM   TB_SN_SO_WT_MST                  SO
           , VW_SN_SI                         SI
           /*(
             SELECT o1.inv_org                INV_ORG
                   ,o1.billto_code            BILLTO_CODE
                   ,o1.billto_name            BILLTO_NAME
                   ,o1.order_no               ORDER_NO
                 --  ,o1.pick_no                PICK_NO
                   ,o1.model                  MODEL
                   ,o1.suffix                 SUFFIX
                   ,o1.prod_div               GRADE
                   ,c.serial_no               SERIAL_NO 
             FROM   TB_SN_CDC_HIST            C
                   ,TB_ORDER_SHIP_HIST        O1
             WHERE  c.inv_org               = o1.inv_org(+)
             AND    c.order_no              = o1.order_no(+)
             AND    c.model                 = o1.model(+)
            -- AND    o1.shipto_name          is not null
             AND    SUBSTR(c.serial_no,1,3)>= '209'
             AND    sellout_flag            = 'Y'  
             GROUP  BY
                    o1.inv_org
                   ,o1.billto_code
                   ,o1.billto_name
                   ,o1.order_no
                   ,o1.model
                   ,o1.suffix
                   ,o1.prod_div
                   ,c.serial_no  
           UNION
             SELECT o.inv_org                 INV_ORG
                   ,o.billto_code             BILLTO_CODE
                   ,o.billto_name             BILLTO_NAME
                   ,o.order_no                ORDER_NO
                  -- ,o.pick_no                 PICK_NO
                   ,r.model                   MODEL
                   ,r.suffix                  SUFFIX
                   ,o.prod_div                GRADE                
                   ,r.serial_no               SERIAL_NO
             FROM   TB_SN_RDC_HIST            R
                   ,TB_ORDER_SHIP_HIST        O
             WHERE  r.inv_org               = o.inv_org(+) 
             --AND    r.edi_no                = o.pick_no(+) 
             AND   (r.ship_no               = o.order_no OR r.edi_no                = o.pick_no) --
             AND    r.model                 = o.model(+)
            -- AND    o.shipto_name           is not null
             AND    SUBSTR(r.serial_no,1,3)>= '209'
             AND    sellout_status          = 'Y'  
             GROUP BY
                   o.inv_org
                  ,o.billto_code
                  ,o.billto_name
                  ,o.order_no
                  ,r.model
                  ,r.suffix
                  ,o.prod_div
                  ,r.serial_no  
                                            ) SI*/
           ,(
             SELECT rg.id                     REG_ID
                   ,rg.name                   REGION
                   ,p.name                    PROVINCE
                   ,b.short_name              B_BILL	
                   ,shop_code                 SHOP_CODE
                   ,shop_type                 CUST_TYPE
                   ,DECODE(channel,'Y','DIRECT','INDIRECT')
                                              CHANNEL
                   ,shop_name                 SHOP_NAME
                   ,cell_no#1                 SHOP_CELL
             FROM   TB_CM_SHOP_INF            S 
                   ,TB_CM_REGION              RG
                   ,TB_CM_PROVINCE            P
                   ,TB_CM_BILLTO_INF          B
             WHERE  s.reg_id                = rg.id
             AND    s.province_code         = p.id 
             AND    s.billto_code           = b.billto_code
                                            ) SR
           , (SELECT m1.prod_l1               PROD_L1
                    ,m1.prod_l2               PROD_L2
                    ,m1.prod_l3               PROD_l3
                    ,m1.prod_l4               PROD_l4 
                    ,m1.model                 MODEL
                    ,mp.mrp                   MRP  
              FROM  TB_CM_MODEL_CAT           m1
                   ,TB_CM_MRP                 mp
              WHERE m1.model                = mp.model(+)) 
                                              M
                           
      WHERE  so.serial_no                   = si.serial_no(+)
      AND    so.model                       = si.model(+)
      AND    so.shop_code                   = sr.shop_code(+)
      AND    so.model                       = m.model(+)
      AND    TO_CHAR(so.sellout_date,'YYYYMMDD') BETWEEN p_ds_date AND p_de_date
      AND    sr.shop_code                LIKE DECODE(p_shop_code,         null, '%', '%'||UPPER(p_shop_code)||'%')
      AND    UPPER(sr.shop_name)         LIKE DECODE(p_shop_name,         null, '%', '%'||UPPER(p_shop_name)||'%')
      AND    'NA'||m.prod_l1             LIKE DECODE(p_prod_l1,           null, '%', '%'||UPPER(p_prod_l1)  ||'%')
      AND    'NA'||m.model               LIKE DECODE(p_model,             null, '%', '%'||UPPER(p_model)    ||'%')
      AND    so.incentive_cfm_flag       LIKE p_incentive_flag
      AND    so.sms_yn                   LIKE p_sms_yn
      AND    sr.reg_id                   LIKE v_region_id
      AND    so.serial_no                LIKE DECODE(p_serial_no,         null, '%', '%'||UPPER(p_serial_no)||'%')
      ORDER BY
            so.sellout_date DESC           
           ,sr.region 
           ,sr.shop_code
           ,si.billto_code
           ,m.prod_l1
           ,m.model
           ,so.incentive_cfm_date DESC;
                                
  END PR_SO_DLY_REG_INQ;
  -----------------------------------------------------------------------------------------------------
  --                          PROCEDURE SO-SMS HISTORY DAILY INQUIRY
  -----------------------------------------------------------------------------------------------------
  PROCEDURE PR_SO_DLY_HIS_INQ(
            out_cur           OUT            SYS_REFCURSOR      
           ,p_df_date         IN             VARCHAR2      DEFAULT TO_CHAR(sysdate, 'YYYYMMDD')
           ,p_dt_date         IN             VARCHAR2      DEFAULT TO_CHAR(sysdate, 'YYYYMMDD')
           ,p_hist_type       IN             VARCHAR2      DEFAULT pk_sms_mst.const_cmdcode_so
           ,p_user_region     IN             VARCHAR2      DEFAULT '00'
           ,p_region_id       IN             VARCHAR2      DEFAULT '%'
           ,p_resp_type       IN             VARCHAR2      DEFAULT '%'
           ,p_shop_cell       IN             VARCHAR2      DEFAULT null
           ,p_mo_msg          IN             VARCHAR2      DEFAULT null)
           
  -----------------------------------------------------------------------------------------------------
  IS
    v_region_id                              VARCHAR2(02)  :=      null;
  -----------------------------------------------------------------------------------------------------
  BEGIN
    IF        p_hist_type                    = pk_sms_mst.const_cmdcode_so THEN
      IF      p_user_region                  = '00' THEN
              v_region_id                   := p_region_id;
      ELSE
              v_region_id                   := p_user_region;
      END IF;
      OPEN out_cur FOR
        SELECT CASE WHEN p_user_region       = '00' THEN 
               DECODE(r.name, null, '--',r.name) END 
                                               REGION
              ,DECODE(s.shop_name,null,'--',s.shop_name)                     
                                               SHOP_NAME
              ,so.shop_cell                    SHOP_CELL
              ,m.prod_l1                       PRODUCT
              ,m.model                         MODEL 
              ,so.serial_no                    SERIAL_NO
              ,so.receive_date                 RECEIVE_DATE
              ,so.mo_msgbody                   RECEIVED_MSG
              ,mr.purpose                      RESP_TYPE
              ,pk_sms_mst.FN_SMS_TRANS(so.resp_msg)
                                               REPPLIED_MSG
        FROM   TB_SN_SO_WT_HIST                SO
              ,(SELECT shop_code               SHOP_CODE
                      ,shop_name               SHOP_NAME 
                      ,reg_id                  REG_ID
                FROM   TB_CM_SHOP_INF
                UNION
                SELECT inst_code               SHOP_CODE
                      ,inst_name               SHOP_NAME 
                      ,region                  REG_ID
                FROM   TB_CM_AC_INSTALLER_INF) S
              ,TB_CM_REGION                    R
              ,TB_CM_MODEL_CAT                 M
              ,TB_MT_SMS_RESP_MSG              MR
        WHERE  so.shop_code                  = s.shop_code(+)
        AND    so.model                      = m.model(+)
        AND    s.reg_id                      = r.id(+)
        AND    so.resp_type                  = mr.resp_type(+)
      --  AND    so.cmdcode                    = p_hist_type
        AND    r.id(+)                    LIKE v_region_id
        AND    TO_CHAR(so.receive_date,'YYYYMMDD') BETWEEN p_df_date AND  p_dt_date
        AND    so.resp_type               LIKE p_resp_type
        AND    so.shop_cell               LIKE DECODE(p_shop_cell, null, '%', '%'||p_shop_cell    ||'%')
        AND    UPPER(so.mo_msgbody)       LIKE DECODE(p_mo_msg   , null, '%', '%'||UPPER(p_mo_msg)||'%')      
      ORDER BY
            so.receive_date DESC
           ,r.name
           ,s.shop_name;            
    ELSE
      OPEN out_cur FOR
        SELECT end_user_cell                   ENDUSER_CELL
              ,m.prod_l1                       PRODUCT
              ,m.model                         MODEL
              ,serial_no                       SERIAL_NO
              ,receive_date                    RECEIVE_DATE
              ,mo_msgbody                      RECEIVED_MSG
              ,mr.purpose                      RESP_TYPE
              ,pk_sms_mst.FN_SMS_TRANS(resp_msg)
                                               RESPONE_MSG         
        FROM   TB_SN_SO_WT_HIST                SH
              ,TB_CM_MODEL_CAT                 M
              ,TB_MT_SMS_RESP_MSG              MR
        WHERE  sh.model                      = m.model(+)
        AND    sh.resp_type                  = mr.resp_type  
        AND    cmdcode                       = p_hist_type
        AND    TO_CHAR(receive_date,'YYYYMMDD') BETWEEN p_df_date AND  p_dt_date
        AND    sh.resp_type               LIKE p_resp_type
        AND    end_user_cell              LIKE DECODE(p_shop_cell, null, '%', '%'||p_shop_cell||'%')
        AND    mo_msgbody                 LIKE DECODE(p_mo_msg   , null, '%', '%'||p_mo_msg   ||'%')        
        ORDER BY
              receive_date DESC;
    END IF;    
  END PR_SO_DLY_HIS_INQ;  
  -----------------------------------------------------------------------------------------------------
  --                          PROCEDURE NON-CONFIRMED INCENTIVE INQUIRY
  -----------------------------------------------------------------------------------------------------
  PROCEDURE PR_SO_NON_INCT_INQ(
            out_cur            OUT           SYS_REFCURSOR
           ,p_f_date           IN            VARCHAR2      DEFAULT TO_CHAR(TRUNC(sysdate,'Day') +1, 'YYYYMMDD')
           ,p_t_date           IN            VARCHAR2      DEFAULT TO_CHAR(TRUNC(sysdate,'Day') +7, 'YYYYMMDD')
           ,p_shop_name        IN            VARCHAR2      DEFAULT null
           ,p_product          IN            VARCHAR2      DEFAULT '%'
           ,p_model            IN            VARCHAR2      DEFAULT '%'
           ,p_serial_no        IN            VARCHAR2      DEFAULT '%'
           ,p_user_region      IN            VARCHAR2      DEFAULT '00'
           ,p_region_id        IN            VARCHAR2      DEFAULT '%')  
  -----------------------------------------------------------------------------------------------------
  IS
            v_region_id                      VARCHAR2(02)  :=      null;
  -----------------------------------------------------------------------------------------------------
  BEGIN
    IF      p_user_region                  = '00' THEN
            v_region_id                   := p_region_id;
    ELSE
            v_region_id                   := p_user_region;
    END IF;  
        
    OPEN out_cur FOR
      SELECT CASE WHEN p_user_region       = '00' THEN r.name END
                                             REGION
            ,s.shop_name                     SHOP_NAME
            ,m.prod_l1                       PRODUCT
            ,m.model                         MODEL
            ,so.serial_no                    SERIAL_NO
            ,so.shop_cell                    SHOP_CELL
            ,so.sellin_date                  SELLIN_DATE
            ,so.sellout_date                 SELLOUT_DATE
            ,TO_CHAR(so.point,'999,999,999') POINT
            ,TO_CHAR(so.amt,'999,999,999')   AMT
      FROM   TB_SN_SO_WT_MST                 SO
            ,TB_CM_SHOP_INF                  S
            ,TB_CM_MODEL_CAT                 M
            ,TB_CM_REGION                    R
      WHERE  s.shop_code                   = so.shop_code
      AND    s.reg_id                      = r.id
      AND    so.model                      = m.model
      AND    s.reg_id                   LIKE v_region_id
      AND    so.incentive_cfm_period       = 'NA'
      AND    TO_CHAR(so.sellout_date,'YYYYMMDD') BETWEEN p_f_date AND p_t_date
      AND    s.shop_name                LIKE DECODE(p_shop_name, null, '%','%'||UPPER(p_shop_name)||'%')
      AND    m.prod_l1                  LIKE UPPER(p_product)
      AND    m.model                    LIKE UPPER(p_model)
      AND    so.serial_no               LIKE UPPER(p_serial_no)
      ORDER BY
             r.name 
            ,s.shop_name
            ,m.prod_l1
            ,m.model;
  END PR_SO_NON_INCT_INQ;
  -----------------------------------------------------------------------------------------------------
  --                           PROCEDURE SO DEALER VERIFICATION INQ
  -----------------------------------------------------------------------------------------------------
  PROCEDURE PR_MAKE_DEALER_SO
  IS
    v_resp_type                 VARCHAR2(10);
    v_resp_msg                  VARCHAR2(160);
    v_dup_check                 VARCHAR2(01);
    v_wt_resp                   WT_RESP_TYPE := WT_RESP_TYPE(null, null, null);
    
  BEGIN
    FOR cur1 IN ( SELECT        shop_code
                               ,serial_no
                               ,model
                               ,sellin_date
                               ,sellout_date
                               ,eu_cell
                               ,edi_date
                               ,edi_no
                   FROM         TB_SN_SO_DEALERS
                   WHERE        vrf_flag          = 'Y'
                   AND          cfm_flag          = 'N')
    LOOP
      BEGIN
        SELECT                  'OK'
        INTO                    v_dup_check
        FROM                    TB_SN_SO_WT_MST
        WHERE                   serial_no         = cur1.serial_no
        AND                     model             = cur1.model;
      
      EXCEPTION
        WHEN NO_DATA_FOUND THEN  
          v_resp_type     := pk_sms_mst.const_so_sn_vl;
          v_resp_msg      := pk_sms_mst.FN_GET_MT_RESPONSE( v_resp_type ,'$' || cur1.model || ', Serial ' || cur1.serial_no);
          
          pk_sms_mst.PR_UPDATE_SN_SO_WT_MST (cur1.shop_code    ,null
                                            ,cur1.serial_no    ,cur1.model
                                            ,null              ,cur1.sellin_date
                                            ,cur1.sellout_date ,null
                                            ,v_resp_type       ,v_resp_msg
                                            ,'N'               ,cur1.eu_cell      
                                            ,'Y'               ,'Y');
          
          --Update SO history
          pk_sms_mst.PR_UPDATE_SN_SO_WT_HIST(cur1.serial_no   , v_resp_type 
                                            ,v_resp_msg       , 'MC-SO'      
                                            ,TO_DATE(cur1.edi_date,'YYYYMMDD')          
                                            ,null             , cur1.eu_cell          
                                            ,'N'              , cur1.edi_no                  
                                            ,'EDI'            , cur1.model           
                                            , cur1.shop_code  ,'Y');
                                         
          UPDATE TB_SN_RDC_HIST
          SET    sellout_status     = 'Y'
                ,sellout_date       = cur1.sellout_date
          WHERE  serial_no          = cur1.serial_no
          AND    model              = cur1.model;
                  
          UPDATE TB_SN_CDC_HIST
          SET    sellout_flag       = 'Y'
                ,sellout_date       = cur1.sellout_date
          WHERE  serial_no          = cur1.serial_no
          AND    model              = cur1.model;
      END;
      
      UPDATE TB_SN_SO_DEALERS
      SET    cfm_flag               = 'Y'
      WHERE  serial_no              = cur1.serial_no
      AND    model                  = cur1.model;
          
      COMMIT; 
      
      v_wt_resp     := pk_sms_mst.FN_GET_WT_RESPONSE(cur1.serial_no , cur1.model);
      IF v_wt_resp.wt_resp_flag = 'O' THEN
        IF v_wt_resp.wt_resp_year = 1 THEN
          v_resp_type   := const_wt_12_vl;
        ELSIF v_wt_resp.wt_resp_year = 2 THEN
          v_resp_type   := const_wt_24_vl;
        END IF;  
      END IF;                      
      v_resp_msg    := pk_sms_mst.FN_GET_MT_RESPONSE(v_resp_type ,v_wt_resp.wt_resp_msg);
      
      INSERT INTO      TB_MT_HIST(
                        moseq                        ,cell_no
                       ,cmdcode                      ,resp_type
                       ,msgbody                      ,mo_msgbody
                       ,mo_receive_time              ,mt_seq)
      VALUES                      (      
                        '0'                          , cur1.eu_cell
                       ,pk_sms_mst.const_cmdcode_wt  , v_resp_type
                       ,v_resp_msg                   , 'MC-EDI'
                       ,TO_DATE(cur1.edi_date,'YYYYMMDD'), mt_seq.nextval);
      COMMIT;      
      --Update WT history
      pk_sms_mst.PR_UPDATE_SN_SO_WT_HIST (cur1.serial_no      , v_resp_type 
                                         ,v_resp_msg          , 'MC-EDI'
                                         ,TO_DATE(cur1.edi_date,'YYYYMMDD')         
                                         ,null                , cur1.eu_cell          
                                         ,'Y'                 , cur1.edi_no
                                         ,'EDI'               , cur1.model          
                                         ,cur1.shop_code      ,'Y');                                                                                                              
    END LOOP;                       
  END PR_MAKE_DEALER_SO;
  -----------------------------------------------------------------------------------------------------   
  PROCEDURE PR_SO_DEALER_INQ(
            out_cur          OUT                               SYS_REFCURSOR
           ,p_shop_code      IN                                VARCHAR2     DEFAULT null
           ,p_shop_name      IN                                VARCHAR2     DEFAULT null
           ,p_f_dt           IN                                VARCHAR2     DEFAULT TO_CHAR(sysdate,'YYYYMMDD')
           ,p_t_dt           IN                                VARCHAR2     DEFAULT TO_CHAR(sysdate +1,'YYYYMMDD')
           ,p_vrf_flag       IN                                VARCHAR2     DEFAULT null
           ,p_cfm_flag       IN                                VARCHAR2     DEFAULT 'N'
           ,p_user_region    IN                                VARCHAR2     DEFAULT '00'
           ,p_region_id      IN                                VARCHAR2     DEFAULT '%')
  IS
            v_region_id                                                VARCHAR2(02):= null;
  BEGIN
    IF p_user_region                                         = '00' THEN
      v_region_id                                           := p_region_id;
    ELSE
      v_region_id                                           := p_user_region;
    END IF;
    
    OPEN out_cur FOR
    SELECT  CASE WHEN p_user_region                          = '00' THEN rg.name END
                                                               REGION
           ,dl.edi_no                                          EDI_NO
           ,si.serial_no                                       SERIAL_NO
           ,si.model                                           MODEL
           ,si.suffix                                          SUFFIX
           ,TO_CHAR(dl.sellin_date,'YYYYMMDD')                 SELLIN_DATE
           ,TO_CHAR(dl.sellout_date,'YYYYMMDD')                SELLOUT_DATE
           ,dl.eu_cell                                       user_cell
           ,dl.vrf_flag                                        VRF_FLAG
           ,dl.cfm_flag                                        CFM_FLAG
           ,dl.cfm_user                                        CFM_USER
           ,dl.cfm_comment                                     CFM_COMMENT                                                    
           ,sh.shop_code                                       SHOP_CODE
           ,sh.shop_name                                       SHOP_NAME
           ,sh.cell_no#1                                       SHOP_CELL
           ,si.billto_name                                     BILL_NAME
    FROM    TB_CM_SHOP_INF                                     sh
           ,TB_CM_REGION                                       rg
           ,TB_SN_SO_DEALERS                                   dl
           ,(SELECT o1.billto_code              BILLTO_CODE
                   ,o1.billto_name              BILLTO_NAME
                   ,o1.model                    MODEL
                   ,o1.suffix                   SUFFIX 
                   ,c.serial_no                 SERIAL_NO 
             FROM   TB_SN_CDC_HIST              C
                   ,TB_ORDER_SHIP_HIST          O1
             WHERE  c.inv_org(+)              = o1.inv_org
             AND    c.order_no(+)             = o1.order_no
             AND    c.model(+)                = o1.model
             AND    SUBSTR(c.serial_no,1,3)  >= '209'
            -- AND    sellout_flag              = 'N'
           UNION
             SELECT o.billto_code               BILLTO_CODE
                   ,o.billto_name               BILLTO_NAME
                   ,o.model                     MODEL 
                   ,o.suffix                    SUFFIX
                   ,r1.serial_no                SERIAL_NO
             FROM   TB_SN_RDC_HIST              R1
                   ,TB_ORDER_SHIP_HIST          O
             WHERE  r1.inv_org(+)             = o.inv_org 
             AND    (r1.ship_no               = o.order_no OR r1.edi_no              = o.pick_no)
             --AND    r1.edi_no(+)              = o.pick_no
             AND    r1.model(+)               = o.model
             AND    SUBSTR(r1.serial_no,1,3) >= '209' ) si
            -- AND    sellout_status            = 'N')           si
    WHERE   sh.reg_id                                        = rg.id
    AND     sh.shop_code                                     = dl.shop_code
    AND     dl.serial_no                                     = si.serial_no(+)
    AND     dl.model                                         = si.model(+)
    AND     TO_CHAR(dl.sellout_date,'YYYYMMDD')        BETWEEN p_f_dt AND p_t_dt
    AND     dl.vrf_flag                                   LIKE DECODE(p_vrf_flag, null,'%',p_vrf_flag)
    AND     dl.cfm_flag                                   LIKE p_cfm_flag
    AND     rg.id                                         LIKE v_region_id
    AND     sh.shop_code                                  LIKE DECODE(p_shop_code, null,'%','%'||UPPER(p_shop_code)||'%')
    AND     UPPER(sh.shop_name)                           LIKE DECODE(p_shop_name, null,'%','%'||UPPER(p_shop_name)||'%');                                                           
             
  END       PR_SO_DEALER_INQ;                                            
  -----------------------------------------------------------------------------------------------------
  --                           PROCEDURE INVENTORY AGING (MONTH) INQUIRY BY YEAR
  -----------------------------------------------------------------------------------------------------
  PROCEDURE PR_INV_AG_MTH_INQ(    
            out_cur           OUT                              SYS_REFCURSOR
           ,p_yyyy            IN                               VARCHAR2      DEFAULT TO_CHAR(sysdate,'YYYY')
           ,p_billto_code     IN                               VARCHAR2      DEFAULT '%'
           ,p_product         IN                               VARCHAR2      DEFAULT '%'
           ,p_model           IN                               VARCHAR2      DEFAULT '%'
           ,p_user_region     IN                               VARCHAR2      DEFAULT '00'
           ,p_region_id       IN                               VARCHAR2      DEFAULT '%')
  -----------------------------------------------------------------------------------------------------        
  IS
            v_jan_s                                            VARCHAR2(10) := p_yyyy|| '0101';
            v_jan_e                                            VARCHAR2(10) := TO_CHAR(LAST_DAY(TO_DATE(v_jan_s,'YYYYMMDD')),'YYYYMMDD');
               
            v_feb_s                                            VARCHAR2(10) := p_yyyy|| '0201';
            v_feb_e                                            VARCHAR2(10) := TO_CHAR(LAST_DAY(TO_DATE(v_feb_s,'YYYYMMDD')),'YYYYMMDD');
               
            v_mar_s                                            VARCHAR2(10) := p_yyyy|| '0301';
            v_mar_e                                            VARCHAR2(10) := TO_CHAR(LAST_DAY(TO_DATE(v_mar_s,'YYYYMMDD')),'YYYYMMDD');
               
            v_apr_s                                            VARCHAR2(10) := p_yyyy|| '0401';
            v_apr_e                                            VARCHAR2(10) := TO_CHAR(LAST_DAY(TO_DATE(v_apr_s,'YYYYMMDD')),'YYYYMMDD'); 
              
            v_may_s                                            VARCHAR2(10) := p_yyyy|| '0501';
            v_may_e                                            VARCHAR2(10) := TO_CHAR(LAST_DAY(TO_DATE(v_may_s,'YYYYMMDD')),'YYYYMMDD');
              
            v_jun_s                                            VARCHAR2(10) := p_yyyy|| '0601';
            v_jun_e                                            VARCHAR2(10) := TO_CHAR(LAST_DAY(TO_DATE(v_jun_s,'YYYYMMDD')),'YYYYMMDD');
              
            v_jul_s                                            VARCHAR2(10) := p_yyyy|| '0701';
            v_jul_e                                            VARCHAR2(10) := TO_CHAR(LAST_DAY(TO_DATE(v_jul_s,'YYYYMMDD')),'YYYYMMDD');
              
            v_aug_s                                            VARCHAR2(10) := p_yyyy|| '0801';
            v_aug_e                                            VARCHAR2(10) := TO_CHAR(LAST_DAY(TO_DATE(v_aug_s,'YYYYMMDD')),'YYYYMMDD');
              
            v_sep_s                                            VARCHAR2(10) := p_yyyy|| '0901';
            v_sep_e                                            VARCHAR2(10) := TO_CHAR(LAST_DAY(TO_DATE(v_sep_s,'YYYYMMDD')),'YYYYMMDD');
              
            v_oct_s                                            VARCHAR2(10) := p_yyyy|| '1001';
            v_oct_e                                            VARCHAR2(10) := TO_CHAR(LAST_DAY(TO_DATE(v_oct_s,'YYYYMMDD')),'YYYYMMDD');
              
            v_nov_s                                            VARCHAR2(10) := p_yyyy|| '1101';
            v_nov_e                                            VARCHAR2(10) := TO_CHAR(LAST_DAY(TO_DATE(v_nov_s,'YYYYMMDD')),'YYYYMMDD');
              
            v_dec_s                                            VARCHAR2(10) := p_yyyy|| '1201';
            v_dec_e                                            VARCHAR2(10) := TO_CHAR(LAST_DAY(TO_DATE(v_dec_s,'YYYYMMDD')),'YYYYMMDD');
              
            v_region_id                                        VARCHAR2(02) := null;
  -----------------------------------------------------------------------------------------------------
  BEGIN  
    IF p_user_region                                         = '00' THEN
      v_region_id                                           := p_region_id;
    ELSE
      v_region_id                                           := p_user_region;
    END IF;
      
    OPEN out_cur FOR 
      SELECT  CASE WHEN p_user_region                        = '00' THEN aa.region END
                                                               REGION
             ,aa.billto_code                                   BILL_TO
             ,aa.billto_name                                   BILL_NAME
             ,aa.product                                       PRODUCT
             ,aa.model                                         MODEL
             ,TO_CHAR(SUM(aa.order_qty),'99G999G999','NLS_NUMERIC_CHARACTERS = '',.')
                                                               STOCK
             ,TO_CHAR(SUM(aa.order_qty) - SUM(ACM),'99G999G999','NLS_NUMERIC_CHARACTERS = '',.')  
                                                               INVENTORY
             ,TO_CHAR(SUM(ACM),'99G999G999','NLS_NUMERIC_CHARACTERS = '',.')           
                                                               ACM
             ,SUM(aa.JAN)                                      JAN
             ,SUM(aa.FEB)                                      FEB
             ,SUM(aa.MAR)                                      MAR
             ,SUM(aa.APR)                                      APR
             ,SUM(aa.MAY)                                      MAY
             ,SUM(aa.JUN)                                      JUN
             ,SUM(aa.JUL)                                      JUL
             ,SUM(aa.AUG)                                      AUG
             ,SUM(aa.SEP)                                      SEP
             ,SUM(aa.OCT)                                      OCT
             ,SUM(aa.NOV)                                      NOV
             ,SUM(aa.DEC)                                      DEC
      FROM  (SELECT si.region                                  REGION
                   ,si.billto_code                             BILLTO_CODE
                   ,si.billto_name                             BILLTO_NAME
                   ,si.product                                 PRODUCT
                   ,si.model                                   MODEL
                   ,si.order_qty                          ORDER_QTY
                   ,CASE WHEN (TO_CHAR(so.sellout_date, 'YYYYMMDD') >=v_jan_s) AND (TO_CHAR(so.sellout_date, 'YYYYMMDD') <=v_jan_e) THEN COUNT(si.serial_no) ELSE 0 END 
                                                               JAN
                   ,CASE WHEN (TO_CHAR(so.sellout_date, 'YYYYMMDD') >=v_feb_s) AND (TO_CHAR(so.sellout_date, 'YYYYMMDD') <=v_feb_e) THEN COUNT(si.serial_no) ELSE 0 END 
                                                               FEB
                   ,CASE WHEN (TO_CHAR(so.sellout_date, 'YYYYMMDD') >=v_mar_s) AND (TO_CHAR(so.sellout_date, 'YYYYMMDD') <=v_mar_e) THEN COUNT(si.serial_no) ELSE 0 END 
                                                               MAR
                   ,CASE WHEN (TO_CHAR(so.sellout_date, 'YYYYMMDD') >=v_apr_s) AND (TO_CHAR(so.sellout_date, 'YYYYMMDD') <=v_apr_e) THEN COUNT(si.serial_no) ELSE 0 END 
                                                               APR
                   ,CASE WHEN (TO_CHAR(so.sellout_date, 'YYYYMMDD') >=v_may_s) AND (TO_CHAR(so.sellout_date, 'YYYYMMDD') <=v_may_e) THEN COUNT(si.serial_no) ELSE 0 END 
                                                               MAY
                   ,CASE WHEN (TO_CHAR(so.sellout_date, 'YYYYMMDD') >=v_jun_s) AND (TO_CHAR(so.sellout_date, 'YYYYMMDD') <=v_jun_e) THEN COUNT(si.serial_no) ELSE 0 END 
                                                               JUN
                   ,CASE WHEN (TO_CHAR(so.sellout_date, 'YYYYMMDD') >=v_jul_s) AND (TO_CHAR(so.sellout_date, 'YYYYMMDD') <=v_jul_e) THEN COUNT(si.serial_no) ELSE 0 END 
                                                               JUL
                   ,CASE WHEN (TO_CHAR(so.sellout_date, 'YYYYMMDD') >=v_aug_s) AND (TO_CHAR(so.sellout_date, 'YYYYMMDD') <=v_aug_e) THEN COUNT(si.serial_no) ELSE 0 END 
                                                               AUG
                   ,CASE WHEN (TO_CHAR(so.sellout_date, 'YYYYMMDD') >=v_sep_s) AND (TO_CHAR(so.sellout_date, 'YYYYMMDD') <=v_sep_e) THEN COUNT(si.serial_no) ELSE 0 END 
                                                               SEP
                   ,CASE WHEN (TO_CHAR(so.sellout_date, 'YYYYMMDD') >=v_oct_s) AND (TO_CHAR(so.sellout_date, 'YYYYMMDD') <=v_oct_e) THEN COUNT(si.serial_no) ELSE 0 END 
                                                               OCT
                   ,CASE WHEN (TO_CHAR(so.sellout_date, 'YYYYMMDD') >=v_nov_s) AND (TO_CHAR(so.sellout_date, 'YYYYMMDD') <=v_nov_e) THEN COUNT(si.serial_no) ELSE 0 END 
                                                               NOV
                   ,CASE WHEN (TO_CHAR(so.sellout_date, 'YYYYMMDD') >=v_dec_s) AND (TO_CHAR(so.sellout_date, 'YYYYMMDD') <=v_dec_e) THEN COUNT(si.serial_no) ELSE 0 END 
                                                               DEC
                   ,CASE WHEN (TO_CHAR(so.sellout_date, 'YYYYMMDD') >=v_jan_s) AND (TO_CHAR(so.sellout_date, 'YYYYMMDD') <=v_dec_e) THEN COUNT(si.serial_no) ELSE 0 END 
                                                               ACM                                            
             FROM
                  (
                    SELECT rg.name                             REGION
                          ,s.billto_code                       BILLTO_CODE
                          ,s.billto_name                       BILLTO_NAME
                          ,m.prod_l1                           PRODUCT 
                          ,m.model                             MODEL
                          ,order_qty                           ORDER_QTY
                          ,s.serial_no                         SERIAL_NO
                    FROM          
                      (
                        SELECT o1.inv_org                      INV_ORG
                              ,o1.billto_code                  BILLTO_CODE
                              ,o1.billto_name                  BILLTO_NAME
                              ,o1.order_no                     ORDER_NO
                              ,o1.model                        MODEL 
                              ,o1.request_arrival_date         DELIVERY_DATE
                              ,SUM(o1.release_qty)             ORDER_QTY
                              ,c.serial_no                     SERIAL_NO 
                        FROM   TB_SN_CDC_HIST                  C
                              ,TB_ORDER_SHIP_HIST              O1
                        WHERE  c.inv_org(+)                  = o1.inv_org
                        AND    c.order_no(+)                 = o1.order_no
                        AND    c.model(+)                    = o1.model
                        AND    SUBSTR(c.serial_no,1,3)      >= '209'
                        AND    sellout_flag                  = 'Y'
                        GROUP BY
                               o1.inv_org 
                              ,o1.order_no
                              ,o1.billto_code              
                              ,o1.billto_name              
                              ,o1.model
                              ,o1.request_arrival_date
                              ,c.serial_no                 
                       UNION
                        SELECT o.inv_org                       INV_ORG
                              ,o.billto_code                   BILLTO_CODE
                              ,o.billto_name                   BILLTO_NAME
                              ,o.order_no                      ORDER_NO
                              ,o.model                         MODEL 
                              ,o.request_arrival_date          DELIVERY_DATE
                              ,SUM(o.release_qty)              ORDER_QTY
                              ,r.serial_no                     SERIAL_NO
                        FROM   TB_SN_RDC_HIST                  R
                              ,TB_ORDER_SHIP_HIST              O
                        WHERE  r.inv_org(+)                  = o.inv_org
                        AND   (r.ship_no                     = o.order_no OR r.edi_no           = o.pick_no)                     
                        --AND    r.edi_no(+)                   = o.pick_no
                        AND    r.model(+)                    = o.model
                        AND    SUBSTR(r.serial_no,1,3)      >= '209'
                        AND    sellout_status                = 'Y'
                        GROUP BY
                               o.inv_org
                              ,o.order_no
                              ,o.billto_code               
                              ,o.billto_name               
                              ,o.model
                              ,o.request_arrival_date             
                              ,r.serial_no)                    S
                       ,       TB_CM_MODEL_CAT                 M
                       ,       TB_CM_REGION                    RG
                       ,     (
                               SELECT DECODE(LTRIM(RTRIM(low_price_group)),'NORTH','01'
                                            ,DECODE(LTRIM(RTRIM(low_price_group)),'MIDDLE','02'
                                                   ,DECODE(LTRIM(RTRIM(low_price_group)),'SOUTH','03')))
                                                               REGION_ID
                                     ,billto_code              BILLTO_CODE
                               FROM   TB_CM_BILLTO_INF
                               WHERE  low_price_group IS NOT NULL)   B
                    WHERE s.model                            = m.model
                    AND   s.billto_code                      = b.billto_code
                    AND   b.region_id                        = rg.id
                    AND   b.region_id                     LIKE v_region_id
                    AND   m.prod_l1                         != 'MC'  
                    AND   TO_CHAR(s.delivery_date,'YYYYMMDD') BETWEEN v_jan_s AND v_dec_e
                    GROUP BY
                          rg.name
                         ,s.billto_code                     
                         ,s.billto_name                     
                         ,m.prod_l1
                         ,m.model
                         ,order_qty
                         ,serial_no     )                      SI
                    ,(
                         SELECT so1.sellout_date               SELLOUT_DATE
                               ,so1.serial_no                  SERIAL_NO
                         FROM   TB_SN_SO_WT_MST                SO1
                               ,TB_CM_SHOP_INF                 SO2
                         WHERE  so1.shop_code                = so2.shop_code
                         AND    so2.reg_id                LIKE v_region_id)  
                                                               SO
               WHERE    si.serial_no                         = so.serial_no(+)
               AND      si.billto_code                    LIKE p_billto_code
               AND      si.product                        LIKE UPPER(p_product)
               AND      si.model                          LIKE UPPER(p_model)
               GROUP BY 
                        si.region
                       ,si.billto_code
                       ,si.billto_name
                       ,si.product
                       ,si.model
                       ,si.order_qty 
                       ,so.sellout_date)                       AA
          GROUP BY
                  aa.region
                 ,aa.billto_code
                 ,aa.billto_name
                 ,aa.product
                 ,aa.model
              --   ,aa.order_qty
          ORDER BY
                  aa.region
                 ,aa.billto_code
                 ,aa.product
                 ,aa.model
                 ,SUM(aa.order_qty)    DESC
                 ,SUM(aa.acm)          DESC;
  END PR_INV_AG_MTH_INQ;      
  -----------------------------------------------------------------------------------------------------
  --                           PROCEDURE WEEKLY INVENTORY AGING INQUIRY BY MONTH 
  -----------------------------------------------------------------------------------------------------
  PROCEDURE PR_INV_AG_WKL_INQ( 
            out_cur           OUT                                SYS_REFCURSOR
           ,p_yyyymm          IN                                 VARCHAR2      DEFAULT TO_CHAR(sysdate,'YYYYMM')
           ,p_billto_code     IN                                 VARCHAR2      DEFAULT '%'
           ,p_product         IN                                 VARCHAR2      DEFAULT '%'
           ,p_model           IN                                 VARCHAR2      DEFAULT '%'
           ,p_user_region     IN                                 VARCHAR2      DEFAULT '00'
           ,p_region_id       IN                                 VARCHAR2      DEFAULT '%' )
  ----------------------------------------------------------------------------------------------------
  IS
            v_ms_date                                            DATE          := TO_DATE(p_yyyymm||'01','YYYYMMDD');
            
            v_w1s_date                           VARCHAR2(10)  := TO_CHAR(TRUNC(v_ms_date,'Day')+1,'YYYYMMDD');
            v_w1e_date                           VARCHAR2(10)  := TO_CHAR(TRUNC(TO_DATE(v_w1s_date,'YYYYMMDD'),'Day') +7,'YYYYMMDD');
            
            v_w2s_date                           VARCHAR2(10)  := TO_CHAR(TRUNC(TO_DATE(v_w1e_date,'YYYYMMDD'),'Day') +1,'YYYYMMDD');
            v_w2e_date                           VARCHAR2(10)  := TO_CHAR(TRUNC(TO_DATE(v_w2s_date,'YYYYMMDD'),'Day') +7,'YYYYMMDD');
            
            v_w3s_date                           VARCHAR2(10)  := TO_CHAR(TRUNC(TO_DATE(v_w2e_date,'YYYYMMDD'),'Day') +1,'YYYYMMDD');
            v_w3e_date                           VARCHAR2(10)  := TO_CHAR(TRUNC(TO_DATE(v_w3s_date,'YYYYMMDD'),'Day') +7,'YYYYMMDD');
            
            v_w4s_date                           VARCHAR2(10)  := TO_CHAR(TRUNC(TO_DATE(v_w3e_date,'YYYYMMDD'),'Day') +1,'YYYYMMDD');
            v_w4e_date                           VARCHAR2(10)  := TO_CHAR(TRUNC(TO_DATE(v_w4s_date,'YYYYMMDD'),'Day') +7,'YYYYMMDD');
            
            v_w5s_date                           VARCHAR2(10)  := TO_CHAR(TRUNC(TO_DATE(v_w4e_date,'YYYYMMDD'),'Day') +1,'YYYYMMDD');
            v_w5e_date                           VARCHAR2(10)  := TO_CHAR(TRUNC(TO_DATE(v_w5s_date,'YYYYMMDD'),'Day') +7,'YYYYMMDD');
            
            v_region_id                                          VARCHAR2(02)  := null;
  ----------------------------------------------------------------------------------------------------
  BEGIN
    IF      p_user_region                                      = '00' THEN
            v_region_id                                       := p_region_id;
    ELSE
            v_region_id                                       := p_user_region;
    END IF;
      
    OPEN out_cur FOR
       SELECT   CASE WHEN p_user_region                        = '00' THEN aa.region END
                                                                 REGION 
               ,aa.billto_code                                   BILL_TO
               ,aa.billto_name                                   BILL_NAME
               ,aa.product                                       PRODUCT
               ,aa.model                                         MODEL
               ,SUM(aa.order_qty)                                STOCK
               ,SUM(aa.order_qty) - SUM(ACM)                     INVENTORY
               ,SUM(ACM)                                         ACM
               ,SUM(aa.w1)                                       W1
               ,SUM(aa.w2)                                       W2
               ,SUM(aa.w3)                                       W3
               ,SUM(aa.w4)                                       W4
               ,SUM(aa.w5)                                       W5
       FROM   (
               SELECT si.region                                  REGION
                     ,si.billto_code                             BILLTO_CODE
                     ,si.billto_name                             BILLTO_NAME
                     ,si.product                                 PRODUCT
                     ,si.model                                   MODEL
                     ,si.order_qty                               ORDER_QTY
                     ,CASE WHEN (TO_CHAR(so.sellout_date, 'YYYYMMDD') >= v_w1s_date AND TO_CHAR(so.sellout_date, 'YYYYMMDD') <= v_w1e_date) THEN
                           COUNT(si.serial_no) ELSE 0 END   W1
                     ,CASE WHEN (TO_CHAR(so.sellout_date, 'YYYYMMDD') >= v_w2s_date AND TO_CHAR(so.sellout_date, 'YYYYMMDD') <= v_w2e_date) THEN 
                           COUNT(si.serial_no) ELSE 0 END   W2
                     ,CASE WHEN (TO_CHAR(so.sellout_date, 'YYYYMMDD') >= v_w3s_date AND TO_CHAR(so.sellout_date, 'YYYYMMDD') <= v_w3e_date) THEN 
                           COUNT(si.serial_no) ELSE 0 END   W3
                     ,CASE WHEN (TO_CHAR(so.sellout_date, 'YYYYMMDD') >= v_w4s_date AND TO_CHAR(so.sellout_date, 'YYYYMMDD') <= v_w4e_date) THEN 
                           COUNT(si.serial_no) ELSE 0 END   W4
                     ,CASE WHEN (TO_CHAR(so.sellout_date, 'YYYYMMDD') >= v_w5s_date AND TO_CHAR(so.sellout_date, 'YYYYMMDD') <= v_w5e_date) THEN 
                           COUNT(si.serial_no) ELSE 0 END   W5      
                     ,CASE WHEN (TO_CHAR(so.sellout_date, 'YYYYMMDD') >= v_w1s_date AND TO_CHAR(so.sellout_date, 'YYYYMMDD') <= v_w5e_date) THEN
                           COUNT(si.serial_no) ELSE 0 END   ACM                                  
               FROM (                       
                      SELECT rg.name                             REGION 
                            ,s.billto_code                       BILLTO_CODE
                            ,s.billto_name                       BILLTO_NAME
                            ,m.prod_l1                           PRODUCT 
                            ,m.model                             MODEL
                            ,order_qty                           ORDER_QTY
                            ,s.serial_no                         SERIAL_NO
                      FROM        
                           (
                              SELECT o1.inv_org                  INV_ORG
                                    ,o1.billto_code              BILLTO_CODE
                                    ,o1.billto_name              BILLTO_NAME
                                    ,o1.order_no                 ORDER_NO
                                    ,o1.pick_no                  PICK_NO
                                    ,o1.model                    MODEL 
                                    ,o1.request_arrival_date     DELIVERY_DATE
                                    ,SUM(o1.release_qty)         ORDER_QTY
                                    ,c.serial_no                 SERIAL_NO 
                              FROM   TB_SN_CDC_HIST              C
                                    ,TB_ORDER_SHIP_HIST          O1
                              WHERE  c.inv_org(+)              = o1.inv_org
                              AND    c.order_no(+)             = o1.order_no
                              AND    c.model(+)                = o1.model
                              AND    SUBSTR(c.serial_no,1,3)  >= '209'
                              AND    sellout_flag              = 'Y'
                              GROUP BY
                                     o1.inv_org 
                                    ,o1.order_no
                                    ,o1.billto_code              
                                    ,o1.billto_name              
                                    ,o1.pick_no
                                    ,o1.model
                                    ,o1.request_arrival_date             
                                    ,c.serial_no                 
                             UNION
                              SELECT o.inv_org                   INV_ORG
                                    ,o.billto_code               BILLTO_CODE
                                    ,o.billto_name               BILLTO_NAME
                                    ,o.order_no                  ORDER_NO
                                    ,o.pick_no                   PICK_NO
                                    ,o.model                     MODEL 
                                    ,o.request_arrival_date      DELIVERY_DATE
                                    ,SUM(o.release_qty)          ORDER_QTY
                                    ,r.serial_no                 SERIAL_NO
                              FROM   TB_SN_RDC_HIST              R
                                    ,TB_ORDER_SHIP_HIST          O
                              WHERE  r.inv_org(+)              = o.inv_org
                              AND   (r.ship_no                 = o.order_no OR r.edi_no           = o.pick_no)
                             -- AND    r.edi_no(+)               = o.pick_no
                              AND    r.model(+)                = o.model
                              AND    SUBSTR(r.serial_no,1,3)  >= '209'
                              AND    sellout_status            = 'Y'
                              GROUP BY
                                     o.inv_org
                                    ,o.order_no
                                    ,o.billto_code               
                                    ,o.billto_name               
                                    ,o.pick_no
                                    ,o.model
                                    ,o.request_arrival_date             
                                    ,r.serial_no    )            S
                             ,       TB_CM_MODEL_CAT             M
                             ,       TB_CM_REGION                RG
                             ,(
                               SELECT DECODE(LTRIM(RTRIM(low_price_group)),'NORTH','01'
                                            ,DECODE(LTRIM(RTRIM(low_price_group)),'MIDDLE','02'
                                                   ,DECODE(LTRIM(RTRIM(low_price_group)),'SOUTH','03')))
                                                                 REGION_ID
                                     ,billto_code                BILLTO_CODE
                               FROM   TB_CM_BILLTO_INF
                               WHERE  low_price_group IS NOT NULL) B
                        WHERE s.model                          = m.model
                        AND   s.billto_code                    = b.billto_code
                        AND   b.region_id                      = rg.id
                        AND   b.region_id                   LIKE v_region_id
                        AND   m.prod_l1                       != 'MC'
                        AND   TO_CHAR(s.delivery_date,'YYYYMMDD') BETWEEN v_w1s_date AND v_w5e_date                        
                        GROUP BY
                              rg.name
                             ,s.billto_code                     
                             ,s.billto_name                     
                             ,m.prod_l1
                             ,m.model
                             ,order_qty
                             ,serial_no     )                  SI
                       ,(
                         SELECT so1.sellout_date               SELLOUT_DATE
                               ,so1.serial_no                  SERIAL_NO
                         FROM   TB_SN_SO_WT_MST                SO1
                               ,tb_cm_shop_inf                 SO2
                         WHERE  so1.shop_code                = so2.shop_code
                         AND    so2.reg_id                LIKE v_region_id)  
                                                               SO
             WHERE    si.serial_no                           = so.serial_no(+)
             AND      si.billto_code                      LIKE p_billto_code
             AND      si.product                          LIKE UPPER(p_product)
             AND      si.model                            LIKE UPPER(p_model)
             GROUP BY 
                      si.region
                     ,si.billto_code
                     ,si.billto_name
                     ,si.product
                     ,si.model
                     ,si.order_qty
                     ,so.sellout_date )                        AA 
        GROUP BY
                aa.region
               ,aa.billto_code
               ,aa.billto_name
               ,aa.product
               ,aa.model
      --         ,aa.order_qty
        ORDER BY
                aa.region
               ,aa.billto_code
               ,aa.product
               ,aa.model
               ,SUM(aa.order_qty)  DESC
               ,SUM(aa.acm) DESC ;
  END  PR_INV_AG_WKL_INQ;   
  -----------------------------------------------------------------------------------------------------
  --                           PROCEDURE DAILY INVENTORY INQUIRY BY WEEK
  -----------------------------------------------------------------------------------------------------
  PROCEDURE PR_INV_AG_DLY_INQ(   
            out_cur            OUT                             SYS_REFCURSOR
           ,p_ws_date          IN                              VARCHAR2      DEFAULT TO_CHAR(TRUNC(sysdate, 'Day') +1,'YYYYMMDD') 
           ,p_we_date          IN                              VARCHAR2      DEFAULT TO_CHAR(TRUNC(sysdate, 'Day') +7,'YYYYMMDD')
           ,p_billto_code      IN                              VARCHAR2      DEFAULT '%'
           ,p_product          IN                              VARCHAR2      DEFAULT '%'
           ,p_model            IN                              VARCHAR2      DEFAULT '%'
           ,p_user_region      IN                              VARCHAR2      DEFAULT '00'
           ,p_region_id        IN                              VARCHAR2      DEFAULT '%')
  ----------------------------------------------------------------------------------------------------
  IS
    v_region_id                                                VARCHAR2(02) := null;
  ----------------------------------------------------------------------------------------------------
  BEGIN
    IF p_user_region                                         = '00' THEN
      v_region_id                                           := p_region_id;
    ELSE
      v_region_id                                           := p_user_region;
    END IF;
      
    OPEN out_cur FOR
      SELECT   CASE WHEN p_user_region                      = '00' THEN region END
                                                              REGION
                 ,billto_code                                 BILL_TO
                 ,billto_name                                 BILL_NAME
                 ,product                                     PRODUCT
                 ,model                                       MODEL
                 ,SUM(order_qty)                              STOCK
                 ,SUM(order_qty) - SUM(ACM)                   INVENTORY
                 ,SUM(ACM)                                    ACM                
                 ,SUM(mon)                                    MON
                 ,SUM(tue)                                    TUE
                 ,SUM(wed)                                    WED
                 ,SUM(thu)                                    THU
                 ,SUM(fri)                                    FRI
                 ,SUM(sat)                                    SAT
                 ,SUM(sun)                                    SUN
        FROM  (
               SELECT si.region                                REGION
                     ,si.billto_code                           BILLTO_CODE
                     ,si.billto_name                           BILLTO_NAME
                     ,si.product                               PRODUCT
                     ,si.model                                 MODEL
                     ,si.order_qty                             ORDER_QTY
                     ,CASE WHEN (TO_CHAR(so.sellout_date, 'YYYYMMDD') = p_ws_date)    THEN COUNT(si.serial_no) ELSE 0 END MON
                     ,CASE WHEN (TO_CHAR(so.sellout_date, 'YYYYMMDD') = TO_CHAR(TO_DATE(p_ws_date,'YYYYMMDD') +1,'YYYYMMDD')) THEN COUNT(si.serial_no) ELSE 0 END TUE
                     ,CASE WHEN (TO_CHAR(so.sellout_date, 'YYYYMMDD') = TO_CHAR(TO_DATE(p_ws_date,'YYYYMMDD') +2,'YYYYMMDD')) THEN COUNT(si.serial_no) ELSE 0 END WED
                     ,CASE WHEN (TO_CHAR(so.sellout_date, 'YYYYMMDD') = TO_CHAR(TO_DATE(p_ws_date,'YYYYMMDD') +3,'YYYYMMDD')) THEN COUNT(si.serial_no) ELSE 0 END THU
                     ,CASE WHEN (TO_CHAR(so.sellout_date, 'YYYYMMDD') = TO_CHAR(TO_DATE(p_ws_date,'YYYYMMDD') +4,'YYYYMMDD')) THEN COUNT(si.serial_no) ELSE 0 END FRI
                     ,CASE WHEN (TO_CHAR(so.sellout_date, 'YYYYMMDD') = TO_CHAR(TO_DATE(p_ws_date,'YYYYMMDD') +5,'YYYYMMDD')) THEN COUNT(si.serial_no) ELSE 0 END SAT
                     ,CASE WHEN (TO_CHAR(so.sellout_date, 'YYYYMMDD') = TO_CHAR(TO_DATE(p_ws_date,'YYYYMMDD') +6,'YYYYMMDD')) THEN COUNT(si.serial_no) ELSE 0 END SUN
                     ,CASE WHEN (TO_CHAR(so.sellout_date, 'YYYYMMDD')>= p_ws_date)    
                           AND  (TO_CHAR(so.sellout_date, 'YYYYMMDD')<= p_we_date)    THEN COUNT(si.serial_no) ELSE 0 END ACM
               FROM
                    (                       
                      SELECT rg.name                           REGION
                            ,s.billto_code                     BILLTO_CODE
                            ,s.billto_name                     BILLTO_NAME
                            ,m.prod_l1                         PRODUCT 
                            ,m.model                           MODEL
                            ,order_qty                         ORDER_QTY
                            ,s.serial_no                       SERIAL_NO
                      FROM        
                           (
                            SELECT o1.inv_org                  INV_ORG
                                  ,o1.billto_code              BILLTO_CODE
                                  ,o1.billto_name              BILLTO_NAME
                                  ,o1.order_no                 ORDER_NO
                                  ,o1.pick_no                  PICK_NO
                                  ,o1.model                    MODEL 
                                  ,o1.request_arrival_date     DELIVERY_DATE
                                  ,SUM(o1.release_qty)         ORDER_QTY
                                  ,c.serial_no                 SERIAL_NO 
                            FROM   TB_SN_CDC_HIST              C
                                  ,TB_ORDER_SHIP_HIST          O1
                            WHERE  c.inv_org(+)              = o1.inv_org
                            AND    c.order_no(+)             = o1.order_no
                            AND    c.model(+)                = o1.model
                            AND    SUBSTR(c.serial_no,1,3)  >= '209'
                            AND    sellout_flag              = 'Y'
                            GROUP BY
                                   o1.inv_org 
                                  ,o1.order_no
                                  ,o1.billto_code              
                                  ,o1.billto_name              
                                  ,o1.pick_no
                                  ,o1.model
                                  ,o1.request_arrival_date             
                                  ,c.serial_no                 
                           UNION
                            SELECT o.inv_org                   INV_ORG
                                  ,o.billto_code               BILLTO_CODE
                                  ,o.billto_name               BILLTO_NAME
                                  ,o.order_no                  ORDER_NO
                                  ,o.pick_no                   PICK_NO
                                  ,o.model                     MODEL 
                                  ,o.request_arrival_date      DELIVERY_DATE
                                  ,SUM(o.release_qty)          ORDER_QTY
                                  ,r.serial_no                 SERIAL_NO
                            FROM   TB_SN_RDC_HIST              R
                                  ,TB_ORDER_SHIP_HIST          O
                            WHERE  r.inv_org(+)              = o.inv_org 
                            AND   (r.ship_no                 = o.order_no OR r.edi_no             = o.pick_no)
                           -- AND    r.edi_no(+)               = o.pick_no
                            AND    r.model(+)                = o.model
                            AND    SUBSTR(r.serial_no,1,3)  >= '209'
                            AND    sellout_status            = 'Y'
                            GROUP BY
                                   o.inv_org
                                  ,o.order_no
                                  ,o.billto_code               
                                  ,o.billto_name               
                                  ,o.pick_no
                                  ,o.model
                                  ,o.request_arrival_date             
                                  ,r.serial_no    )            S
                          ,        TB_CM_MODEL_CAT             M
                          ,        TB_CM_REGION                RG
                          ,(
                            SELECT DECODE(LTRIM(RTRIM(low_price_group)),'NORTH','01'
                                         ,DECODE(LTRIM(RTRIM(low_price_group)),'MIDDLE','02'
                                                ,DECODE(LTRIM(RTRIM(low_price_group)),'SOUTH','03')))
                                                               REGION_ID
                                  ,billto_code                 BILLTO_CODE
                            FROM   TB_CM_BILLTO_INF
                            WHERE  low_price_group IS NOT NULL) B
                        WHERE s.model                        = m.model
                        AND   s.billto_code                  = b.billto_code
                        AND   b.region_id                    = rg.id
                        AND   b.region_id                 LIKE v_region_id
                        AND   m.prod_l1                     != 'MC'
                        AND   TO_CHAR(s.delivery_date,'YYYYMMDD') BETWEEN p_ws_date AND p_we_date
                        GROUP BY
                              rg.name
                             ,s.billto_code                     
                             ,s.billto_name                     
                             ,m.prod_l1
                             ,m.model
                             ,order_qty
                             ,serial_no     )                  SI
                      , (
                         SELECT so1.sellout_date               SELLOUT_DATE
                               ,so1.serial_no                  SERIAL_NO
                         FROM   TB_SN_SO_WT_MST                SO1
                               ,tb_cm_shop_inf                 SO2
                         WHERE  so1.shop_code                = so2.shop_code
                         AND    so2.reg_id                LIKE v_region_id)                     
                                                               SO
                 WHERE    si.serial_no                       = so.serial_no(+)
                 AND      si.billto_code                  LIKE p_billto_code
                 AND      si.product                      LIKE UPPER(p_product)
                 AND      si.model                        LIKE UPPER(p_model)
                 GROUP BY 
                          si.region
                         ,si.billto_code
                         ,si.billto_name
                         ,si.product
                         ,si.model
                         ,si.order_qty
                         ,so.sellout_date )                    
      GROUP BY
            region
           ,billto_code
           ,billto_name
           ,product
           ,model
         --        ,aa.order_qty
      ORDER BY
            region
           ,billto_code
           ,product
           ,model
           ,SUM(order_qty) DESC;
                 --  ,SUM(aa.acm)       DESC;                                   
  END PR_INV_AG_DLY_INQ;
  -----------------------------------------------------------------------------------------------------
  --                           PROCEDURE INQUIRY INVENTORY SUMMARIZATION
  -----------------------------------------------------------------------------------------------------   
  PROCEDURE PR_INV_SUM_INQ(      
            out_cur                OUT                  SYS_REFCURSOR
           ,p_ds_date              IN                   VARCHAR2      DEFAULT TO_CHAR(TRUNC(sysdate, 'Day') +1,'YYYYMMDD') 
           ,p_de_date              IN                   VARCHAR2      DEFAULT TO_CHAR(TRUNC(sysdate, 'Day') +7,'YYYYMMDD')
           ,p_billto_code          IN                   VARCHAR2      DEFAULT '%'
           ,p_product              IN                   VARCHAR2      DEFAULT '%'
           ,p_model                IN                   VARCHAR2      DEFAULT '%'
           ,p_user_region          IN                   VARCHAR2      DEFAULT '00'
           ,p_region_id            IN                   VARCHAR2      DEFAULT '%')
  ----------------------------------------------------------------------------------------------------
  IS
    v_region_id                                          VARCHAR2(02) := null;
  -----------------------------------------------------------------------------------------------------
  BEGIN
    IF p_user_region                                   = '00' THEN
      v_region_id                                     := p_region_id;
    ELSE
      v_region_id                                     := p_user_region;
    END IF;
      
    OPEN out_cur FOR
      SELECT CASE WHEN p_user_region                   = '00' THEN si.region END
                                                         REGION
            ,si.billto_code                              BILLTO_CODE
            ,si.billto_name                              BILLTO_NAME
            ,si.product                                  PRODUCT
            ,si.model                                    MODEL
            ,TO_CHAR(order_qty,'999G999','NLS_NUMERIC_CHARACTERS = '',.')                                   
                                                         ORDER_QTY
            ,TO_CHAR(COUNT(so.serial_no),'999G999','NLS_NUMERIC_CHARACTERS = '',.')
                                                         SELLOUT_QTY
            ,TO_CHAR(order_qty - COUNT(so.serial_no),'999G999')             
                                                         INVENTORY 
      FROM  (                       
              SELECT rg.name                             REGION
                    ,s.billto_code                       BILLTO_CODE
                    ,s.billto_name                       BILLTO_NAME
                    ,m.prod_l1                           PRODUCT 
                    ,m.model                             MODEL
                    ,order_qty                           ORDER_QTY
                    ,s.serial_no                         SERIAL_NO
              FROM        
                   (
                      SELECT o1.inv_org                  INV_ORG
                            ,o1.billto_code              BILLTO_CODE
                            ,o1.billto_name              BILLTO_NAME
                            ,o1.order_no                 ORDER_NO
                            ,o1.pick_no                  PICK_NO
                            ,o1.model                    MODEL 
                            ,o1.request_arrival_date     DELIVERY_DATE
                            ,SUM(o1.release_qty)         ORDER_QTY
                            ,c.serial_no                 SERIAL_NO 
                      FROM   TB_SN_CDC_HIST              C
                            ,TB_ORDER_SHIP_HIST          O1
                      WHERE  c.inv_org(+)              = o1.inv_org
                      AND    c.order_no(+)             = o1.order_no
                      AND    c.model(+)                = o1.model
                      AND    SUBSTR(c.serial_no,1,3)  >= '209'
                      AND    sellout_flag              = 'Y'
                      GROUP BY
                             o1.inv_org 
                            ,o1.order_no
                            ,o1.billto_code              
                            ,o1.billto_name              
                            ,o1.pick_no
                            ,o1.model
                            ,o1.request_arrival_date             
                            ,c.serial_no                 
                     UNION
                      SELECT o.inv_org                   INV_ORG
                            ,o.billto_code               BILLTO_CODE
                            ,o.billto_name               BILLTO_NAME
                            ,o.order_no                  ORDER_NO
                            ,o.pick_no                   PICK_NO
                            ,o.model                     MODEL 
                            ,o.request_arrival_date      DELIVERY_DATE
                            ,SUM(o.release_qty)          ORDER_QTY
                            ,r.serial_no                 SERIAL_NO
                      FROM   TB_SN_RDC_HIST              R
                            ,TB_ORDER_SHIP_HIST          O
                      WHERE  r.inv_org(+)              = o.inv_org 
                      AND   (r.ship_no                 = o.order_no OR r.edi_no               = o.pick_no)
                     -- AND    r.edi_no(+)               = o.pick_no
                      AND    r.model(+)                = o.model
                      AND    SUBSTR(r.serial_no,1,3)  >= '209'
                      AND    sellout_status            = 'Y'
                      GROUP BY
                             o.inv_org
                            ,o.order_no
                            ,o.billto_code               
                            ,o.billto_name               
                            ,o.pick_no
                            ,o.model
                            ,o.request_arrival_date             
                            ,r.serial_no    )            S
                     ,       TB_CM_MODEL_CAT             M
                     ,       TB_CM_REGION                RG
                     ,      (
                             SELECT DECODE(LTRIM(RTRIM(low_price_group)),'NORTH','01'
                                          ,DECODE(LTRIM(RTRIM(low_price_group)),'MIDDLE','02'
                                                 ,DECODE(LTRIM(RTRIM(low_price_group)),'SOUTH','03')))
                                                         REGION_ID
                                   ,billto_code          BILLTO_CODE
                             FROM   TB_CM_BILLTO_INF
                             WHERE  low_price_group IS NOT NULL ) 
                                                        B
                WHERE s.model                         = m.model
                AND   s.billto_code                   = b.billto_code
                AND   b.region_id                     = rg.id
                AND   b.region_id                  LIKE v_region_id
                AND   m.prod_l1                      != 'MC'
                AND   TO_CHAR(s.delivery_date,'YYYYMMDD') BETWEEN p_ds_date AND p_de_date
                GROUP BY
                      rg.name
                     ,s.billto_code                     
                     ,s.billto_name                     
                     ,m.prod_l1
                     ,m.model
                     ,order_qty
                     ,serial_no     )                  SI
             ,(
               SELECT so1.sellout_date                 SELLOUT_DATE
                     ,so1.serial_no                    SERIAL_NO
               FROM   TB_SN_SO_WT_MST  SO1
                     ,tb_cm_shop_inf   SO2
               WHERE  so1.shop_code                  = so2.shop_code
               AND    so2.reg_id                  LIKE v_region_id)
                                                       SO
        WHERE si.serial_no                           = so.serial_no(+)
        AND   si.billto_code                      LIKE UPPER(p_billto_code)
        AND   si.product                          LIKE UPPER(p_product)
        AND   si.model                            LIKE UPPER(p_model)
        GROUP BY 
               si.region
              ,si.billto_code
              ,si.billto_name
              ,si.product
              ,si.model
              ,si.order_qty
        ORDER BY
               si.region
              ,si.billto_code
              ,si.product
              ,si.model
              ,si.order_qty        DESC
              ,COUNT(si.serial_no) DESC;
  END PR_INV_SUM_INQ;  
  -----------------------------------------------------------------------------------------------------
  --                          PROCEDURE INQUIRY PRODUCT WARRANTY PERIOD 
  -----------------------------------------------------------------------------------------------------
  PROCEDURE PR_PROD_WT_PER_INQ(
            out_cur          OUT              SYS_REFCURSOR
           ,p_serial_no      IN               VARCHAR2
           ,p_model          IN               VARCHAR2 DEFAULT '%')
  -----------------------------------------------------------------------------------------------------
  IS
  -----------------------------------------------------------------------------------------------------
  BEGIN
    OPEN out_cur FOR
      SELECT user_cell                        END_USER_CELL
            ,product                          PRODUCT
            ,model                            MODEL
            ,suffix                           SUFFIX
            ,serial_no                        SERIAL_NO
            ,receive_date                     REQUEST_DATE
            ,TO_CHAR(wt_start_date,'YYYY-MM-DD')                    
                                              START_DATE
            ,TO_CHAR(wt_end_date,'YYYY-MM-DD')                      
                                              END_DATE
            ,TO_CHAR(TRUNC(wt_end_date,'Day') - TRUNC(sysdate,'Day'),'999,999')
                                              REMAIN_DAYS
            ,so_cell                          SHOP_CELL 
      FROM  (      
              SELECT m.prod_l1                PRODUCT
                    ,m.model                  MODEL
                    ,m.suffix                 SUFFIX     
                    ,s.serial_no              SERIAL_NO 
                    ,s.receive_date           RECEIVE_DATE
                    ,s.wt_start_date          WT_START_DATE
                    ,s.wt_end_date            WT_END_DATE
                    ,s.shop_cell              SO_CELL
                    ,s.end_user_cell          USER_CELL
              FROM  (
                     SELECT DISTINCT(serial_no) SERIAL_NO
                           ,model               MODEL
                           ,MAX(create_date)    RECEIVE_DATE
                           ,MIN(wt_start_date)  WT_START_DATE
                           ,MIN(wt_end_date)    WT_END_DATE
                           ,end_user_cell       END_USER_CELL
                           ,shop_cell           SHOP_CELL
                     FROM  (      
                            SELECT serial_no
                                  ,model
                                  ,create_date
                                  ,wt_start_date
                                  ,wt_end_date
                                  ,end_user_cell
                                  ,shop_cell
                            FROM   TB_SN_SO_WT_MST
                            UNION
                            SELECT serial_no
                                  ,model
                                  ,create_date
                                  ,wt_start_date
                                  ,wt_end_date
                                  ,end_user_cell
                                  ,''--shop_cell
                            FROM   TB_SN_SO_WT_MST0)
                     GROUP BY
                            model
                           ,serial_no
                           ,end_user_cell
                           ,shop_cell        
                                            ) S
                    ,tb_cm_model_cat          M
              WHERE  s.model                = m.model
              GROUP BY 
                     m.prod_l1
                    ,m.model
                    ,m.suffix 
                    ,s.serial_no
                    ,s.receive_date
                    ,s.wt_start_date
                    ,s.wt_end_date
                    ,s.shop_cell
                    ,s.end_user_cell )       AA 
      WHERE serial_no                      = UPPER(p_serial_no)
      AND   model                       LIKE UPPER(p_model)      
      GROUP BY 
             product
            ,model
            ,suffix
            ,serial_no
            ,receive_date
            ,serial_no
            ,wt_start_date
            ,wt_end_date
            ,so_cell
            ,user_cell
      UNION
      SELECT end_user_cell                   USER_CELL
            ,''                              PRODUCT  
            ,''                              MODEL
            ,''                              SUFFIX
            ,serial_no                       SERIAL_NO
            ,receive_date                    SELLIN_DATE
            ,'-'                             START_DATE
            ,'-'                             END_DATE
            ,'-'                             REMAIN_DAYS
            ,'-'                             SHOP_CELL 
       FROM  TB_SN_SO_WT_HIST
       WHERE resp_type         IN (pk_sms_mst.const_wt_sn_vl)
       AND   serial_no =UPPER(p_serial_no);      
  END PR_PROD_WT_PER_INQ;    
  -----------------------------------------------------------------------------------------------------
  --                          PROCEDURE SO PENDING INQUIRY
  -----------------------------------------------------------------------------------------------------
  PROCEDURE PR_PROD_REG_PND_INQ(     
            out_cur             OUT       SYS_REFCURSOR
           ,p_ds_date           IN        VARCHAR2 DEFAULT TO_CHAR(TRUNC(sysdate,'Day') +1 ,'YYYYMMDD')
           ,p_de_date           IN        VARCHAR2 DEFAULT TO_CHAR(TRUNC(sysdate,'Day') +7 ,'YYYYMMDD')
           ,p_product           IN        VARCHAR2 DEFAULT null
           ,p_model             IN        VARCHAR2 DEFAULT null
           ,p_serial_no         IN        VARCHAR2 DEFAULT null
           ,p_sms_yn            IN        VARCHAR2 DEFAULT null
           ,p_pnd_type          IN        VARCHAR2 DEFAULT pk_sms_mst.const_sn_pnd_vl
           ,p_shop_name         IN        VARCHAR2 DEFAULT null
           ,p_user_region       IN        VARCHAR2 DEFAULT '00'
           ,p_region_id         IN        VARCHAR2 DEFAULT '%')
  -----------------------------------------------------------------------------------------------------
  IS
    v_region_id                           VARCHAR2(02) := null;
  -----------------------------------------------------------------------------------------------------
  BEGIN
    IF p_user_region                    = '00' THEN
      v_region_id                      := p_region_id;
    ELSE
      v_region_id                      := p_user_region;
    END IF;
          
    OPEN out_cur FOR
      SELECT CASE WHEN p_user_region    = '00' THEN r.name END
                                          REGION  
              ,p.reject_flag              REJECT_STATUS
              ,s.shop_code                SHOP_CODE
              ,UPPER(s.shop_name)         SHOP_NAME
              ,UPPER(pv.name)             PROVINCE
              ,UPPER(b.short_name)        BELONG_TO_CUSTOMER
              ,b.billto_code              BILLTO_CUSTOMER
              ,m.prod_l1                  PRODUCT
              ,m.model                    MODEL
              ,p.serial_no                SERIAL_NO
              ,p.receive_date             RECEIVE_DATE
              ,p.shop_cell                SHOP_CELL
              ,p.end_user_cell            ENDUSER_CELL
              ,pk_sms_mst.FN_SMS_TRANS(p.resp_msg)                 
                                          MSG
              ,p.sms_yn                   SMS_YN
      FROM     TB_SN_PND_HIST             P
              ,TB_CM_SHOP_INF             S
              ,TB_CM_MODEL_CAT            M
              ,Tb_CM_REGION               R
              ,TB_CM_BILLTO_INF           B
              ,TB_CM_PROVINCE             PV 
      WHERE    p.shop_code              = s.shop_code(+)
      AND      s.billto_code            = b.billto_code(+)
      AND      s.province_code          = pv.id(+)
      AND      p.model                  = m.model(+)
      AND      s.reg_id                 = r.id(+)
      AND      p.cfm_flag               = 'N'
      AND      p.reject_flag            = 'N'
      AND      p.cfm_user               = 'SYS' 
      AND      s.reg_id              LIKE v_region_id
      AND      TO_CHAR(p.receive_date,'YYYYMMDD') BETWEEN p_ds_date AND p_de_date
      AND      m.prod_l1(+)          LIKE DECODE(p_product,       null, '%', '%'||UPPER(p_product)  ||'%')
      AND      m.model(+)            LIKE DECODE(p_model,         null, '%', '%'||UPPER(p_model)    ||'%')
      AND      p.serial_no           LIKE DECODE(p_serial_no,     null, '%', '%'||UPPER(p_serial_no)||'%')
      AND      p.sms_yn              LIKE DECODE(p_sms_yn,        null, '%', '%'||p_sms_yn          ||'%')
      AND      p.pnd_type               = p_pnd_type
      AND      UPPER(s.shop_name(+)) LIKE DECODE(p_shop_name,     null, '%', '%'||UPPER(p_shop_name)||'%');
  END PR_PROD_REG_PND_INQ;
  -----------------------------------------------------------------------------------------------------
  --                          PROCEDURE WT PENDING INQUIRY
  -----------------------------------------------------------------------------------------------------
  PROCEDURE PR_PROD_WT_PND_INQ(     
            out_cur             OUT       SYS_REFCURSOR
           ,p_ds_date           IN        VARCHAR2 DEFAULT TO_CHAR(sysdate,'YYYYMMDD')
           ,p_de_date           IN        VARCHAR2 DEFAULT TO_CHAR(sysdate,'YYYYMMDD')
           ,p_product           IN        VARCHAR2 DEFAULT null
           ,p_model             IN        VARCHAR2 DEFAULT null
           ,p_serial_no         IN        VARCHAR2 DEFAULT null
           ,p_pnd_type          IN        VARCHAR2 DEFAULT pk_sms_mst.const_wt_sn_nvl)
  ---------------------------------------------------------------------------------------------------
  IS
    
  -----------------------------------------------------------------------------------------------------
  BEGIN
    OPEN out_cur FOR
      SELECT   m.prod_l1                  PRODUCT
              ,m.model                    MODEL
              ,p.serial_no                SERIAL_NO
              ,p.receive_date             RECEIVE_DATE
              ,p.shop_cell                SHOP_CELL
              ,p.end_user_cell            ENDUSER_CELL
              ,p.resp_msg                 MSG
      FROM     tb_sn_pnd_hist             P
              ,tb_cm_model_cat            M 
      WHERE    p.model                  = m.model(+)
      AND      p.cfm_flag               = 'N'
      AND      TO_CHAR(p.receive_date,'YYYYMMDD') BETWEEN p_ds_date AND p_de_date
      AND      m.prod_l1(+)          LIKE DECODE(p_product,       null, '%', '%'||UPPER(p_product)  ||'%')
      AND      m.model(+)            LIKE DECODE(p_model,         null, '%', '%'||UPPER(p_model)    ||'%')
      AND      p.serial_no           LIKE DECODE(p_serial_no,     null, '%', '%'||UPPER(p_serial_no)||'%')
      AND      p.pnd_type               = p_pnd_type;
  END PR_PROD_WT_PND_INQ; 
   
  -----------------------------------------------------------------------------------------------------
  --                          FUNCTION DATA VALIDATE FOR MANUAL ACTIVATION.
  -----------------------------------------------------------------------------------------------------
  FUNCTION FN_MNL_ACT_VAL(
           p_act_type             VARCHAR2
          ,p_serial_no            VARCHAR2
          ,p_model                VARCHAR2
          ,p_active_date          VARCHAR2 )
              
  RETURN                          VARCHAR2
  -----------------------------------------------------------------------------------------------------
  IS
    v_dup_ck                      SN_DUP_TYPE   := SN_DUP_TYPE(null, null, null);
      
    v_return                      VARCHAR2(500) := null;
  -----------------------------------------------------------------------------------------------------
  BEGIN
    IF ((SUBSTR(p_serial_no,5,1)  = 'A' 
      AND 
        SUBSTR(p_serial_no,4,1)  != 'T')  
      AND (p_model               IS NULL))
    OR (p_model                  IS NULL) THEN
      v_return                   := 'F-MODEL';
            
      RETURN                        v_return;
    END IF;
    IF p_act_type                 = 'WARRANTY' THEN
      IF (p_active_date           IS NULL) 
        OR 
         TO_DATE(p_active_date,'YYYYMMDD') > sysdate THEN
           v_return              := 'F-DATE';
            
        RETURN                      v_return;
      END IF;  
    END IF;
    --validate the registered modeL
    BEGIN
      SELECT                        model
      INTO                          v_return
      FROM                          TB_CM_MODEL_CAT
      WHERE  model                = UPPER(p_model);
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        v_return                 := 'F-MODEL';
          
        RETURN                      v_return;  
    END;
    --check the so serial duplication
    v_dup_ck                     := pk_sms_mst.FN_SN_DUP_CHECK( p_serial_no                     ,p_model
                                                               ,pk_sms_mst.const_so_sn_inc_dup  ,'LGDK');
    IF v_dup_ck.DUP_FLAG          = 'D' THEN
      v_return                   := 'F-DUP';
        
      RETURN                        v_return;
    END IF;                                            
      
    v_return                     := 'DONE';
    RETURN                          v_return;          
  END  FN_MNL_ACT_VAL;    
  -----------------------------------------------------------------------------------------------------
  --                          FUNCTION MANUAL ACTIVE DATA SAVE.
  -----------------------------------------------------------------------------------------------------
  FUNCTION FN_MNL_ACT_SO_SAV(
           p_pick_no                VARCHAR2
          ,p_serial_no              VARCHAR2
          ,p_box_no                 VARCHAR2 DEFAULT null
          ,p_model                  VARCHAR2
          ,p_suffix                 VARCHAR2 DEFAULT null
          ,p_prod_date              VARCHAR2
          ,p_act_user               VARCHAR2)
  RETURN                            VARCHAR2
  -----------------------------------------------------------------------------------------------------
  IS                                PRAGMA AUTONOMOUS_TRANSACTION;
    v_return                        VARCHAR2(100) := null;
  -----------------------------------------------------------------------------------------------------
  BEGIN
    INSERT INTO    TB_SN_RDC_HIST(
                                  inv_org
                                 ,edi_no
                                 ,serial_no
                                 ,box_no
                                 ,model_suffix
                                 ,model
                                 ,suffix
                                 ,ship_no
                                 ,line_no
                                 ,out_date)
    VALUES                       (
                                  SUBSTR(p_pick_no,1,3)
                                 ,p_pick_no
                                 ,p_serial_no
                                 ,DECODE(p_box_no, null, p_serial_no, p_box_no)
                                 ,p_model || '.' || p_suffix
                                 ,p_model
                                 ,p_suffix
                                 ,'0'
                                 ,1
                                 ,TO_DATE(p_prod_date,'YYYYMMDD'));
                                                                  
    UPDATE                        TB_SN_PND_HIST
    SET    cfm_user             = p_act_user
          ,cfm_date             = sysdate
    WHERE  serial_no            = p_serial_no
    AND    pnd_type             = pk_sms_mst.const_sn_pnd_vl;
      
    COMMIT;
      
    v_return                   := 'DONE';
    
    RETURN                        v_return;
  EXCEPTION
    WHEN OTHERS THEN
      PR_RAISE_ERR( 'FN_MNL_ACT_SO_SAV' ,DBMS_UTILITY.format_error_backtrace || '/'||SQLERRM ,p_serial_no || p_model);
      
      v_return                 := 'FAIL';   
      RETURN                      v_return;        
  END FN_MNL_ACT_SO_SAV;
  -----------------------------------------------------------------------------------------------------
  --
  -----------------------------------------------------------------------------------------------------
  FUNCTION FN_MNL_ACT_WT_SAV(
           p_serial_no              VARCHAR2
          ,p_model                  VARCHAR2
          ,p_wt_act_date            VARCHAR2 
          ,p_receive_date           VARCHAR2 
          ,p_sellin_date            VARCHAR2 DEFAULT null
          ,p_enduser_cell           VARCHAR2 DEFAULT null   
          ,p_act_user               VARCHAR2
          ,p_vrf_flag               VARCHAR2 DEFAULT 'Y'
          ,p_sms_yn                 VARCHAR2 DEFAULT 'Y')
  RETURN                            VARCHAR2
  IS
    v_wt_start_date                 DATE;
    v_wt_end_date                   DATE;
    v_model_div                     VARCHAR2(030) := null;
    v_temp                          VARCHAR2(100) := null;
    v_wt_resp                       WT_RESP_TYPE  := WT_RESP_TYPE(null, null, null);
    v_wt_resp_type                  VARCHAR2(010) := null;
    v_wt_resp_msg                   VARCHAR2(160) := null;
    
    v_return                        VARCHAR2(100) := null;
  BEGIN
    -- Simulate warranty period by product MNT :2years /Others :1year
    BEGIN
      SELECT                      DISTINCT(prod_l1)
      INTO                        v_model_div
      FROM                        TB_CM_MODEL_CAT
      WHERE  model              = UPPER(p_model)
      GROUP  BY 
                                  prod_l1;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        v_model_div            := 'OTHERS';
      WHEN OTHERS THEN
        v_return               := 'FAIL';       
    END;
-----------------------------------------------------------------------------------------------------
    v_wt_start_date            := TO_DATE(p_wt_act_date,'YYYYMMDD');
    IF v_model_div             IN (pk_sms_mst.const_wt_2ys, pk_sms_mst.const_wt_2yrs) THEN
       v_wt_end_date           := v_wt_start_date + (365-1) * 2;
    ELSE
       v_wt_end_date           := v_wt_start_date + (365-1);   
    END IF;
    --Update the warranty infos  
    BEGIN
      INSERT INTO                 TB_SN_SO_WT_MST0(
                                  model              ,serial_no
                                 ,wt_start_date      ,wt_end_date
                                 ,end_user_cell      ,wt_resp_msg
                                 ,wt_resp_type       ,sms_yn
                                 ,wt_cfm_user        ,sellin_date)
      VALUES                      ( 
                                  p_model            ,p_serial_no
                                 ,v_wt_start_date    ,v_wt_end_date
                                 ,p_enduser_cell     ,null
                                 ,null               ,p_sms_yn
                                 ,p_act_user         ,TO_DATE(p_sellin_date,'YYYYMMDD'));   
           
      COMMIT;
    EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN v_return := 'DUP_VAL_WT/';   
    END;
    --Update pending table
    UPDATE                        TB_SN_PND_HIST
    SET    cfm_flag             = 'Y'
          ,cfm_date             = sysdate
          ,cfm_user             = p_act_user
    WHERE  serial_no            = p_serial_no 
    AND    'ABN'||model      LIKE DECODE(p_model, null, '%', p_model);
        
    COMMIT;      
    IF p_vrf_flag               = 'N' THEN
    -- CASE : The product is not available in store => do update to store for SO 
      BEGIN
        INSERT INTO               TB_SN_RDC_HIST(
                                  inv_org         ,edi_no         
                                 ,serial_no       ,box_no
                                 ,model_suffix    ,model
                                 ,suffix          ,line_no
                                 ,out_date        ,sms_yn)
        VALUES                    (
                                  'WTA'           ,'WTMA'
                                 ,p_serial_no     ,p_serial_no
                                 ,p_model ||'.NA' ,p_model
                                 ,'NA'            ,'1'
                                 ,TO_DATE(p_sellin_date, 'YYYYMMDD') ,p_sms_yn);
        COMMIT;
      EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN v_return := v_return || 'DUP_ON_SN';
      END;                         
    END IF;
    --Reply WT period to end user. 
    v_wt_resp                  := pk_sms_mst.FN_GET_WT_RESPONSE( p_serial_no ,p_model );
    IF (v_wt_resp.wt_resp_flag    IS NOT NULL) 
      AND 
       (v_wt_resp.wt_resp_flag     NOT IN ('M','F')) THEN                                                     
          IF v_wt_resp.wt_resp_year  = 1 THEN
             v_wt_resp_type       := const_wt_12_vl;
          ELSIF v_wt_resp.wt_resp_year  = 2 THEN
             v_wt_resp_type       := const_wt_24_vl;   
          END IF;
          v_wt_resp_msg        := pk_sms_mst.FN_GET_MT_RESPONSE( v_wt_resp_type ,v_wt_resp.wt_resp_msg);

          INSERT INTO             TB_MT_HIST(
                                  moseq                       ,cell_no
                                 ,cmdcode                     ,resp_type
                                 ,msgbody                     ,mo_msgbody
                                 ,mo_receive_time             ,mt_seq)
          VALUES                  (
                                  '0'                         ,p_enduser_cell
                                 ,pk_sms_mst.const_cmdcode_wt ,v_wt_resp_type
                                 ,v_wt_resp_msg               ,v_wt_resp_msg
                                 ,sysdate                     ,mt_seq.nextval);
          COMMIT;
          --Update history
          pk_sms_mst.PR_UPDATE_SN_SO_WT_HIST(
                                  p_serial_no                        ,v_wt_resp_type
                                 ,v_wt_resp_msg                      ,v_wt_resp_msg
                                 ,TO_DATE(p_receive_date,'YYYYMMDD') ,null
                                 ,p_enduser_cell                     ,'Y'                 
                                 ,'0'                                ,pk_sms_mst.const_cmdcode_wt ,p_model
                                 ,null                               ,'Y');
    END IF;
    
    RETURN                        v_return;
  EXCEPTION
    WHEN OTHERS THEN       
      PR_RAISE_ERR( 'FN_MAKE_PND_RESPONSE' ,DBMS_UTILITY.format_error_backtrace || '/'||SQLERRM ,p_serial_no || p_model);
       
      RETURN 'FAIL';                              
  END FN_MNL_ACT_WT_SAV;  
  -----------------------------------------------------------------------------------------------------
  --                          FUNCTION PENDING REJECT
  ----------------------------------------------------------------------------------------------------- 
  FUNCTION FN_MNL_ACT_PND_REJ(
           p_serial_no               VARCHAR2
          ,p_model                   VARCHAR2 DEFAULT null
          ,p_user                    VARCHAR2)
  RETURN                             VARCHAR2
  -----------------------------------------------------------------------------------------------------
  IS                                 PRAGMA AUTONOMOUS_TRANSACTION;
  -----------------------------------------------------------------------------------------------------
  BEGIN
    UPDATE                           tb_sn_pnd_hist
    SET    reject_flag             = 'Y'
          ,reject_user             = p_user
          ,reject_date             = sysdate
    WHERE  serial_no               = p_serial_no
    AND    'ABN'||model         LIKE DECODE(p_model, null, '%','%'||p_model);
    COMMIT;
    
    RETURN                           'DONE';
  EXCEPTION
    WHEN OTHERS THEN
      RETURN                         SQLERRM;                                        
  END FN_MNL_ACT_PND_REJ;  
  -----------------------------------------------------------------------------------------------------
  --                          PROCEDURE SHOP INQUIRY
  ----------------------------------------------------------------------------------------------------- 
  PROCEDURE PR_SHOP_INQ(     
            out_cur        OUT       SYS_REFCURSOR
           ,p_shop_code    IN        VARCHAR2 DEFAULT null
           ,p_shop_name    IN        VARCHAR2 DEFAULT null
           ,p_shop_address IN        VARCHAR2 DEFAULT null
           ,p_shop_cell    IN        VARCHAR2 DEFAULT null
           ,p_use_flag     IN        VARCHAR2 DEFAULT null
           ,p_dealer_yn    IN        VARCHAR2 DEFAULT null
           ,p_user_region  IN        VARCHAR2 DEFAULT '00'
           ,p_region_id    IN        VARCHAR2 DEFAULT '%')
  -----------------------------------------------------------------------------------------------------
  IS
    v_region_id                      VARCHAR2(02):= null;
  -----------------------------------------------------------------------------------------------------
  BEGIN
   IF p_user_region                = '00' THEN
     v_region_id                  := p_region_id;
   ELSE
     v_region_id                  := p_user_region;
   END IF;
         
   OPEN out_cur FOR
     SELECT  CASE WHEN p_user_region = '00' THEN r.name ELSE '' END   
                                     REGION
            ,DECODE(s.channel,'Y','DIRECT','INDIRECT')             
                                     CHANNEL
            ,s.shop_type             CUST_TYPE                                                  
            ,s.shop_code             CUST_CODE
            ,UPPER(s.shop_name)      CUST_NAME
            ,b.short_name            BELONG_TO_CUST
            ,b.billto_code           BELONG_TO_BILL
            ,s.owner_name            OWNER_NAME
            ,UPPER(p.name)           PROVINCE
            ,UPPER(d.name)           DISTRICT
            ,UPPER(s.address)        ADDRESS
            ,s.cell_no#1             CELL#1
            ,s.cell_no#2             CELL#2
            ,s.use_flag              USE_FLAG
            ,s.create_date           CREATE_DATE
     FROM    tb_cm_shop_inf          S
            ,tb_cm_region            R
            ,tb_cm_district          D
            ,tb_cm_province          P
            ,tb_cm_billto_inf        B
     WHERE   s.billto_code         = b.billto_code
     AND     s.reg_id              = r.id
     AND     s.province_code       = p.id
     AND     s.dist_code           = d.id
     AND     s.reg_id           LIKE v_region_id 
     AND     UPPER(s.shop_code) LIKE DECODE(p_shop_code,    null, '%', '%'||UPPER(p_shop_code)   ||'%')
     AND     UPPER(s.shop_name) LIKE DECODE(p_shop_name,    null, '%', '%'||UPPER(p_shop_name)   ||'%')
     AND     UPPER(s.address)   LIKE DECODE(p_shop_address, null, '%', '%'||UPPER(p_shop_address)||'%')
     AND     s.use_flag         LIKE DECODE(p_use_flag,     null, '%', '%'||p_use_flag           ||'%')
     AND     s.channel          LIKE DECODE(p_dealer_yn,    null, '%', '%'||p_dealer_yn          ||'%')
     AND     s.use_flag            = 'Y'
     AND    (   s.cell_no#1     LIKE DECODE(p_shop_cell,    null, '%', '%'||p_shop_cell          ||'%')
             OR s.cell_no#2     LIKE DECODE(p_shop_cell,    null, '%', '%'||p_shop_cell          ||'%'))
     ORDER BY
             r.name        
            ,b.billto_code DESC
            ,s.shop_code   DESC
            ,s.create_date DESC;
  END PR_SHOP_INQ;
  -----------------------------------------------------------------------------------------------------
  --                          PROCEDURE CLOSED TRACING
  -----------------------------------------------------------------------------------------------------
  PROCEDURE PR_ORDER_TRC(  
            out_cur         OUT              SYS_REFCURSOR
           ,p_order_no      IN               VARCHAR2     )
  -----------------------------------------------------------------------------------------------------
  IS
  -----------------------------------------------------------------------------------------------------
  BEGIN
    OPEN out_cur FOR
      SELECT order_no                        ORDER_NO
            ,billto_code                     BILLTO_CODE
            ,billto_name                     BILLTO_NAME
            ,shipto_name                     SHIPTO_NAME
            ,m.prod_l1                       PRODUCT 
            ,m.model                         MODEL
            ,order_qty                       ORDER_QTY
            ,COUNT(s.serial_no)              SERIAL_NO
            ,booked_date                     BOOKED_DATE
            ,ship_method                     SHIP_METHOD
            ,inv_org                         WAREHOUSE
            ,sales_channel                   SALES_CHANNEL
      FROM          
        (
          SELECT o1.inv_org                  INV_ORG
                ,o1.billto_code              BILLTO_CODE
                ,o1.billto_name              BILLTO_NAME
                ,o1.shipto_name              SHIPTO_NAME
                ,o1.order_no                 ORDER_NO
                ,o1.model                    MODEL
                ,SUM(o1.release_qty)         ORDER_QTY 
                ,c.serial_no                 SERIAL_NO 
                ,o1.booked_date              BOOKED_DATE
                ,o1.shipping_method          SHIP_METHOD
                ,o1.saleschannel_name        SALES_CHANNEL
          FROM   TB_SN_CDC_HIST                            C
                ,TB_ORDER_SHIP_HIST                        O1
          WHERE  c.inv_org(+)              = o1.inv_org
          AND    c.order_no(+)             = o1.order_no
          AND    c.model(+)                = o1.model
          AND    SUBSTR(c.serial_no,1,3)  >= '209'
          GROUP BY
                 o1.inv_org                  
                ,o1.billto_code              
                ,o1.billto_name              
                ,o1.shipto_name              
                ,o1.order_no                 
                ,o1.model 
                ,c.serial_no                  
                ,o1.booked_date              
                ,o1.shipping_method          
                ,o1.saleschannel_name        
         UNION
          SELECT o.inv_org                   INV_ORG
                ,o.billto_code               BILLTO_CODE
                ,o.billto_name               BILLTO_NAME
                ,o.shipto_name               SHIPTO_NAME
                ,o.order_no                  ORDER_NO
                ,o.model                     MODEL 
                ,SUM(o.release_qty)          ORDER_QTY
                ,r.serial_no                 SERIAL_NO 
                ,o.booked_date               BOOKED_DATE
                ,o.shipping_method           SHIP_METHOD
                ,o.saleschannel_name         SALES_CHANNEL
          FROM   TB_SN_RDC_HIST              R
                ,TB_ORDER_SHIP_HIST          O
          WHERE  r.inv_org(+)              = o.inv_org 
          AND   (r.ship_no                 = o.order_no OR r.edi_no               = o.pick_no)
         -- AND    r.edi_no(+)               = o.pick_no
          AND    r.model(+)                = o.model
          AND    SUBSTR(r.serial_no,1,3)  >= '209'
          GROUP BY
                 o.inv_org                  
                ,o.billto_code              
                ,o.billto_name              
                ,o.shipto_name              
                ,o.order_no                 
                ,o.model
                ,r.serial_no                  
                ,o.booked_date              
                ,o.shipping_method          
                ,o.saleschannel_name )       S        
                ,TB_CM_MODEL_CAT             M
      WHERE s.model                        = m.model(+)
      AND   s.order_no                     = p_order_no
      GROUP BY
             order_no                        
            ,billto_code                     
            ,billto_name
            ,shipto_name                     
            ,m.prod_l1                        
            ,m.model
            ,order_qty                         
            ,booked_date                     
            ,ship_method                     
            ,inv_org                         
            ,sales_channel
      ORDER BY
             billto_code 
            ,m.prod_l1
            ,m.model
            ,booked_date                     DESC;                   
              
  END PR_ORDER_TRC;
  ----------------------------------------------------------------------------------------------------- 
  --                          PROCEDURE PICK_NO TRACKING
  -----------------------------------------------------------------------------------------------------
  PROCEDURE PR_PICK_TRC(
            out_cur         OUT                    SYS_REFCURSOR
           ,p_billto_code   IN                     VARCHAR2
           ,p_model         IN                     VARCHAR2
           ,p_suffix        IN                     VARCHAR2       DEFAULT '%')
  IS
  -----------------------------------------------------------------------------------------------------
  BEGIN
    OPEN out_cur FOR
      SELECT pick_no                               PICK_NO
            ,billto_name                           BILLTO_NAME
            ,prod_div                              PRODUCT
            ,model                                 MODEL
            ,suffix                                SUFFIX
            ,shipto_name                           SHIPTO_NAME
            ,invoice_no                            INVOICE_NO
            ,invoice_date                          INVOICE_DATE
            ,request_arrival_date                  DELIVERY_DATE
            ,order_qty                             ORDER_QTY
            ,release_qty                           RELEASE_QTY
            
      FROM   TB_ORDER_SHIP_HIST
      WHERE  billto_code                         = p_billto_code
      AND    model                               = p_model
      AND    suffix                           LIKE p_suffix  ;           
  END PR_PICK_TRC;                
  ----------------------------------------------------------------------------------------------------- 
  --                          PROCEDURE SERIAL TRACKING
  -----------------------------------------------------------------------------------------------------
  PROCEDURE PR_SN_TRC(     
            out_cur       OUT                      SYS_REFCURSOR
           ,p_serial_no   IN                       VARCHAR2
           ,p_model       IN                       VARCHAR2 DEFAULT null)
  -----------------------------------------------------------------------------------------------------
  IS
    v_serial_sub                                   VARCHAR2(50);
  -----------------------------------------------------------------------------------------------------
  BEGIN
    OPEN out_cur FOR
      SELECT   DECODE(prod.serial_no, null, si.serial_no, prod.serial_no)
                                                   SERIAL_NO
              ,prod.box_no                         BOX_NO                                                              
              ,prod.prod_model                     PROD_MODEL
              ,prod.prod_suffix                    PROD_SUFFIX
              ,si.model                            SAL_MODEL
              ,si.suffix                           SAL_SUFFIX
              ,si.order_no                         ORDER_NO
              ,prod.prod_date                      PRODUCT_DATE
              ,si.sellin_date                      SELLIN_DATE
       --      ,so.sellout_date                     SELLOUT_DATE
        FROM  (
               SELECT DISTINCT(serial_no)          SERIAL_NO
                     ,box_no                       BOX_NO
                     ,prod_model                   PROD_MODEL
                     ,prod_suffix                  PROD_SUFFIX
                     ,work_order                   WORK_ORDER
                     ,TO_CHAR(prod_date,'YYYY-MM-DD')                    
                                                   PROD_DATE 
               FROM ( 
                     SELECT DECODE(lot_set, NULL, lot_box, lot_set) 
                                                         SERIAL_NO
                            ,lot_box                     BOX_NO
                            ,model                       PROD_MODEL
                            ,suffix                      PROD_SUFFIX
                            ,work_order                  WORK_ORDER
                            ,TO_DATE(SUBSTR(actualtime,1,8),'YYYYMMDD') 
                                                         PROD_DATE
                     FROM    TB_WP_ADDWMSINPLOG@DBL_MES
                     WHERE   lot_set    LIKE UPPER(p_serial_no)
                     AND     actualtime = (SELECT max(actualtime)
                                           FROM   TB_WP_ADDWMSINPLOG@DBL_MES
                                           WHERE  lot_set LIKE UPPER(p_serial_no))
                     UNION
                     SELECT DISTINCT(a.serial_no)        SERIAL_NO
                           ,a.serial_no                  BOX_NO
                           ,c.part_no                    PROD_MODEL
                           ,c.part_no                    PROD_SUFFIX
                           ,a.work_order                 WORK_ORDER
                           ,c.scheduled_completion_date  PROD_DATE
                     FROM lkalsmgr.tbl_serial_no_printed_serial@dbl_mes
                                                         A
                
                         ,(SELECT work_order, serial_no 
                           FROM lkalsmgr.tbl_serial_no_printed_serial@dbl_mes
                           WHERE serial_no LIKE UPPER(p_serial_no)) 
                                                         B
                         ,TB_WO_WORKORDER@DBL_MES        C
                     WHERE  a.work_order   = b.work_order
                     AND    a.serial_no    = b.serial_no
                     AND    a.serial_no_usage LIKE 'DISPLAY_SET%'
                     AND    a.work_order       = c.work_order
               UNION
               SELECT lot_set                      SERIAL_NO
                     ,lot_box                      BOX_NO
                     ,model_code                   MODEL
                     ,suffix                       SUFFIX
                     ,lot_id                       WORK_ORDER
                     ,transfer_date                PROD_DATE
               FROM   vnxx_sn_cbu_hist_if          
               WHERE  lot_set     LIKE UPPER(p_serial_no)))                
                                                   PROD
             ,(
               SELECT o1.inv_org                   INV_ORG
                     ,o1.billto_name               BILLTO_NAME
                     ,o1.shipto_name               SHIPTO_NAME
                     ,o1.order_no                  ORDER_NO
                     ,o1.model                     MODEL
                     ,o1.suffix                    SUFFIX
                     ,c.serial_no                  SERIAL_NO 
                     ,TO_CHAR(o1.request_arrival_date,'YYYY-MM-DD')      
                                                   SELLIN_DATE           
               FROM   tb_sn_cdc_hist               C
                     ,tb_order_ship_hist           O1
               WHERE  c.inv_org                  = o1.inv_org(+)
               AND    c.order_no                 = o1.order_no(+)
               AND    c.model                    = o1.model(+)
               AND    c.serial_no             LIKE UPPER(p_serial_no)
               AND    c.model                 LIKE DECODE(p_model, null, '%', UPPER(p_model))  
           UNION
               SELECT o.inv_org                    INV_ORG
                     ,o.billto_name                BILLTO_NAME
                     ,o.shipto_name                SHIPTO_NAME
                     ,o.order_no                   ORDER_NO
                     ,o.model                      MODEL 
                     ,o.suffix                     SUFFIX
                     ,r.serial_no                  SERIAL_NO 
                     ,TO_CHAR(o.request_arrival_date,'YYYY-MM-DD')       
                                                   SELLIN_DATE
               FROM   tb_sn_rdc_hist               R
                     ,tb_order_ship_hist           O
               WHERE  r.inv_org                  = o.inv_org(+) 
               --AND    r.edi_no                   = o.pick_no(+)
               AND   (r.ship_no                  = o.order_no OR r.edi_no  = o.pick_no)   
               AND    r.model                    = o.model(+)
               AND    r.serial_no             LIKE UPPER(p_serial_no)
               AND    r.model                 LIKE DECODE(p_model, null, '%', UPPER(p_model)))
                                                   SI     
      WHERE    si.serial_no                     = prod.serial_no(+);
  --    AND      si.serial_no                   LIKE UPPER(p_serial_no);
  -----------------------------------------------------------------------------------------------------
      IF out_cur%NOTFOUND THEN
        CLOSE out_cur;
            
        v_serial_sub := SUBSTR(p_serial_no,1,3) || SUBSTR(p_serial_no, 8, LENGTH(p_serial_no)-7);
        OPEN out_cur FOR
          SELECT si.serial_no                        SERIAL_NO
                ,si.model                            SAL_MODEL
                ,prod.prod_model                     PRODUCT_MODEL
                ,si.suffix                           SAL_SUFFIX
                ,prod.prod_suffix                    PRODUCT_SUFFIX
                ,si.order_no                         ORDER_NO
                ,prod.prod_date                      PRODUCT_DATE
                ,si.sellin_date                      SELLIN_DATE
          --   ,so.sellout_date                     SELLOUT_DATE
          FROM  (
                 SELECT DECODE(lot_set, NULL, lot_box, lot_set) 
                                                     SERIAL_NO
                        ,lot_box                     BOX_NO
                        ,model                       PROD_MODEL
                        ,suffix                      PROD_SUFFIX
                        ,work_order                  WORK_ORDER
                        ,TO_DATE(SUBSTR(actualtime,1,8),'YYYYMMDD') 
                                                     PROD_DATE
                 FROM    TB_WP_ADDWMSINPLOG@DBL_MES
                 WHERE   actualtime = (SELECT max(actualtime)
                                       FROM   TB_WP_ADDWMSINPLOG@DBL_MES
                                       WHERE  lot_set LIKE UPPER(p_serial_no))
                 UNION
                 SELECT lot_set                      SERIAL_NO
                       ,lot_box                      BOX_NO
                       ,model_code                   MODEL
                       ,suffix                       SUFFIX
                       ,lot_id                       WORK_ORDER
                       ,transfer_date                PROD_DATE
                 FROM   vnxx_sn_cbu_hist_if          
                 WHERE  lot_set     = UPPER(p_serial_no))              
                                                     PROD
               ,(
                 SELECT o1.inv_org                   INV_ORG
                       ,o1.billto_name               BILLTO_NAME
                       ,o1.shipto_name               SHIPTO_NAME
                       ,o1.order_no                  ORDER_NO
                       ,o1.model                     MODEL
                       ,o1.suffix                    SUFFIX
                       ,c.serial_no                  SERIAL_NO 
                       ,o1.request_arrival_date      SELLIN_DATE           
                 FROM   tb_sn_cdc_hist               C
                       ,tb_order_ship_hist           O1
                 WHERE  c.inv_org                  = o1.inv_org(+)
                 AND    c.order_no                 = o1.order_no(+)
                 AND    c.model                    = o1.model(+)
                 AND    c.serial_no             LIKE v_serial_sub
                 AND    c.model                 LIKE DECODE(p_model, null, '%', UPPER(p_model))
             UNION
                 SELECT o.inv_org                    INV_ORG
                       ,o.billto_name                BILLTO_NAME
                       ,o.shipto_name                SHIPTO_NAME
                       ,o.order_no                   ORDER_NO
                       ,o.model                      MODEL 
                       ,o.suffix                     SUFFIX
                       ,SUBSTR(r.serial_no,1,3) ||   SUBSTR(p_serial_no,4,4) || SUBSTR(r.serial_no, 4, LENGTH(r.serial_no)-3)
                                                     SERIAL_NO 
                       ,o.request_arrival_date       SELLIN_DATE
                 FROM   tb_sn_rdc_hist               R 
                       ,tb_order_ship_hist           O
                 WHERE  r.inv_org                  = o.inv_org(+) 
                 AND   (r.ship_no                  = o.order_no OR r.edi_no                   = o.pick_no)
                 --AND    r.edi_no                   = o.pick_no(+)
                 AND    r.model                    = o.model(+)
                 AND    r.serial_no             LIKE v_serial_sub
                 AND    r.model                 LIKE DECODE(p_model, null, '%', UPPER(p_model)))   
                                                     SI
              /*,(
                 SELECT serial_no                    SERIAL_NO 
                       ,sellout_date                 SELLOUT_DATE
                 FROM   tb_sn_so_wt_mst                                  
                 WHERE  serial_no                  = UPPER(p_serial_no)
                 AND    model                   LIKE UPPER(p_model))   
                                                     SO        */                                          
        WHERE    si.serial_no(+)                    = prod.serial_no;
     --  AND      si.serial_no                      = so.serial_no(+) 
     --   AND      si.serial_no                   LIKE UPPER(p_serial_no);
      END IF;
  END PR_SN_TRC;
  ----------------------------------------------------------------------------------------------------- 
  --                          PROCEDURE PRODUCT MODEL TRACKING
  -----------------------------------------------------------------------------------------------------
  PROCEDURE PR_PROD_MODEL_TRC(
            out_cur       OUT                      SYS_REFCURSOR
           ,p_model       IN                       VARCHAR2)
  IS
  -----------------------------------------------------------------------------------------------------
  BEGIN
    OPEN out_cur FOR
      SELECT part_no                               PROD_MODEL_SUFFIX
            ,sales_model_code                      SALES_MODEL
            ,DECODE(sales_model_suffix, null, SUBSTR(part_no, INSTR(part_no,'.',1) +1, LENGTH(part_no)-INSTR(part_no,'.',1)), sales_model_suffix)
                                                   SALES_SUFFIX           
      FROM   TB_CM_ITEMMASTER@DBL_MES
      WHERE  part_no LIKE '%' || p_model || '%';               
                                      
  END PR_PROD_MODEL_TRC;
  ----------------------------------------------------------------------------------------------------- 
  --                          PROCEDURE BILLTO CODE TRACKING
  -----------------------------------------------------------------------------------------------------
  PROCEDURE PR_BILLTO_TRC(
            out_cur           OUT                  SYS_REFCURSOR
           ,p_bill_shop_name  IN                   VARCHAR2)
  IS
  -----------------------------------------------------------------------------------------------------
  BEGIN
    OPEN out_cur FOR
      SELECT billto_code                           BILLTO_CODE
            ,short_name                            BILLTO_NAME
            ,low_price_group                       REGION
      FROM   TB_CM_BILLTO_INF
      WHERE  full_name                        LIKE '%'||UPPER(p_bill_shop_name)||'%'                        
      UNION
      SELECT b.billto_code                         BILLTO_CODE
            ,b.short_name                          BILLTO_NAME
            ,b.low_price_group                     REGION
      FROM   TB_CM_SHIPTO_INF                      S
            ,TB_CM_BILLTO_INF                      B
      WHERE  s.billto_code                      =  b.billto_code
      AND    s.shipto_name                    LIKE '%'||UPPER(p_bill_shop_name)||'%';                       
  END PR_BILLTO_TRC;                                            
END PK_SOM_MST;
